In SQL Server, what is the purpose of grouping stored procedures?

Question

One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by calling it usp_DoSomethingAwesome;2.

I discovered this when troubleshooting some replication issues (Publisher: SQL 2000 Ent., Dist/Sub: 2008 R2 Ent.) that arose with some of the system-generated Insert, Update, and Delete replication stored procedures.

What is the purpose/thought behind having this "grouping" ability?

Answer 1

This is like overloading a method. Essentially you could create two or more versions of your stored procedure, where each does different things - take a different number of parameters, operate on different tables, have different output, etc.

They are called Numbered Procedures and they are absolutely deprecated (announced since 2005). They are still supported in SQL Server 2012, but some features don't play nicely with them. For example, they are considered a containment breach in Contained Databases, and any procedure numbered > 1 will not be created:

Msg 12829, Level 16, State 1, Procedure blat, Line 1
The stored procedure 'dbo.blat' refers to a group of numbered stored procedures. Numbered stored procedures are not available in contained databases. Please consult the Books Online topic Understanding Contained Databases for more information on contained databases.

Answer 2

The ability (deprecated) to group Stored Procedures seems to exist for a single (and rather silly) purpose: the ability to mass delete via a single DROP statement. According to the SQL Server 2000 MSDN documentation for Creating a Stored Procedure:

Grouping
A procedure can be created with the same name as an existing stored procedure if it is given a different identification number, which allows the procedures to be grouped logically. Grouping procedures with the same name allows them to be deleted at the same time. Procedures used in the same application are often grouped this way. For example, the procedures used with the my_app application might be named my_proc;1, my_proc;2, and so on. Deleting my_proc deletes the entire group. After procedures have been grouped, individual procedures within the group cannot be deleted.

There are no additional benefits to using this construct given that using the same base name doesn't even allow for overloading (signatures do not need to be unique, and no automatic execution routing to a particular "number") and so you still need to use the "number" when executing. Hence the determination of "silly" (and that is probably being way too nice about it ;-).