CODEWITHSUNDEEP
sqlsql-serverstored-procedures
broncofan7
broncofan7

Reputation: 75

Script to create multiple stored procedures

I am testing a scenario with a high number of stored procedures in mssql. Is there a way to script creating ~5000 stored procedures? My attempts have been futile.

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    CREATE PROCEDURE 'SelectAllCustomer'+ convert(varchar(5))  AS SELECT * FROM Customers
    select @id = @id + 1
end
go

Fails with:

Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'PROCEDURE'.

Even just adding a parameter to the procedure name is failing:

CREATE PROCEDURE 'SelectAllCustomer'+ 'test' AS SELECT * FROM Customers

fails with:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'SelectAllCustomer'.

Upvotes: 0

Views: 652

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Here you go. Pretty straight forward.

declare @id int = 1
    , @sql NVARCHAR(MAX)

while @id >=1 and @id <= 1000
begin
    select @sql = 'CREATE PROCEDURE SelectAllCustomer'+ convert(varchar(5), @id) + ' AS SELECT * FROM Customers;'
    exec sp_executesql @sql
    select @id = @id + 1
end

Upvotes: 1

Related Questions