Numbered Stored Procedures in SQL Server

I've come across an unseen feature (for me) in SQL Server, named Numbered Stored Procedures. They are like normal Stored Procedures but there lies some difference. The normal Stored Procedures are unique in their names whereas Numbered Stored Procedures are quite opposite to them in this aspect. 

Numbered Stored Procedures are the ones which exist with the same names and can be with different functionality inside them. To differentiate, each Stored Procedure should be given a number. It is just like Method Overloading which we find in Object Oriented Programming Languages where a number of methods are created with the same name and different parameters. Let's see how this works in SQL Server,

Create a Stored Procedure as follows:

USE AdventureWorks2012

GO

CREATE PROCEDURE Numbered_Procedure;1

AS

BEGIN

    SELECT * FROM Person.Person

END

In the above snippet, note that a number "1" is specified after the name of the Procedure. This means this is the first procedure of the numbered procedures. If you want to create another Stored Procedure with the same name and a different functionality then it goes as follows:

CREATE PROCEDURE Numbered_Procedure;2

AS

BEGIN

    SELECT * FROM Production.Product

END

The above Stored Procedure is the second Stored Procedure with the same name. You cannot create the second procedure without creating the first procedure. If you try to do so then SQL Server raises the following error

Msg 2730, Level 11, State 1, Procedure Numbered_Procedure, Line 1

Cannot create procedure 'Numbered_Procedure' with a group number of 2 because a procedure with the same name and a group number of 1 does not currently exist in the database. Must execute CREATE PROCEDURE 'Numbered_Procedure';1 first.

This means you need to create a procedure with number 1 before going to create the one with number 2. There is even no rule that you should create the procedure with number 2 after creating 1. Creating procedure with number 1 is mandatory. Later number 3 or anything can be created. 

If you take the help of the System Stored Procedure sp_helptext then it shows the definition of all starting from the Procedure with number 1.

EXEC sp_helptext Numbered_Procedure

To get the definition of the other procedures created individually, use the following System View,

SELECT * FROM sys.numbered_procedures

In the above screenshot, you can observe that all the Stored Procedures created have the same "object_id". To execute a particular numbered procedures, you can execute as follows:

EXEC Numbered_Procedure;2

If you don't specify any number in the EXEC statement then the procedure created with number 1 is executed. Each procedure can be modified separately but all the Numbered Procedures created can be dropped with a single DROP statement as,

DROP PROCEDURE Numbered_Procedure

There is no option of dropping a Stored Procedure with a particular number. Microsoft has been mentioning in the product documentation since SQL Server 2005 that this feature will be removed in the next release, but this feature is even present in the recent release SQL Server 2014. To be on safe side, don't use this feature in your production environment.

There is a limitation for these Numbered Procedures. They cannot be used in Contained Databases.

Every feature can be tested with a sample database AdventureWorks available for different versions of SQL Server for download in the following link:

http://msftdbprodsamples.codeplex.com/releases/view/93587