Sql-server – In SQL Server, what is the purpose of grouping stored procedures

sql serverstored-procedures

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?

Best Answer

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.

Related Question