Home » Tip Bank » Database Development » SQL » Transact-sql

Language: SQL
Expertise: Beginner

Mar 7, 2005

Ever created a bunch of stored procedures for testing purposes and went to drop them but couldn't remember all the names you created? A way to avoid this is to group multiple stored procedures under a single name. The following is the syntax for grouping:

create procedure <procname>1
as
 ....
 ....
 GO
create procedure <procname>;2
as
 ....
 ....
 GO

Example:
create procedure <test>;1
as
 ....
 ....
 GO
create procedure <test>;2
as
 ....
 ....
 GO

The number assigned for each stored procedure will vary from 1 TO 32767. If you want to execute the stored procedure:

 
exec test;1
exec test;2 ... etc

Drop all stored procedures using the drop procedure test.

Note: This tip works for all MS SQL 2000 flavors.