Script to create multiple stored procedures-CodePudding

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'.

CodePudding user response：

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