Script to create multiple stored procedures

Question

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

Why are you creating a pile of stored procedures that all do the same thing? That makes no sense. But if you really feel you need to do this you would have to use dynamic sql to generate them. — Sean Lange
– Sean Lange, Commented Jan 26, 2022 at 19:58
Strictly a test scenario. Just need a large number of stored procedures. — broncofan7
– broncofan7, Commented Jan 26, 2022 at 20:04
A customer hit a problem and they have 3500 procedures. Just need to recreate their environment. — broncofan7
– broncofan7, Commented Jan 26, 2022 at 20:06
Gotcha. Kind of odd but sometimes you have to do some odd things for support. ;) As I said previously you would have to use dynamic sql for this. — Sean Lange
– Sean Lange, Commented Jan 26, 2022 at 20:06

Sean Lange · Accepted Answer · 2022-01-26 20:22:19Z

1

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

answered Jan 26, 2022 at 20:22

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Script to create multiple stored procedures

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related