Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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