Throughout my years of work in the IT industry, I have learnt that hanging around in the forums is always a learning experience. Recently, I was looking for a bit of a challenge and went to the unanswered questions section in the Ask module of this site. I stumbled across this question.

The Question

The person who posted the query had inherited a 3rd party database where he found three (3) different stored procedures with the same schema, same name, but with different functionality. However counter-intuitive it may seem, it’s quite possible. Try it out for yourself by using the script shown below:

USE tempdb;
GO

IF OBJECT_ID('dbo.proc_ManipulateNumbers','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_ManipulateNumbers;
GO

--SP #1 - Addition
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 1 
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId,
           'SP #1 - (@iA + @iB) = ' AS Operation, 
           (@iA + @iB) AS Value;
END
GO

--SP #2 - Multiplication
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 2
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId, 
           'SP #2 - (@iA * @iB) = ' AS Operation, 
           (@iA * @iB) AS Value;
END
GO

--SP #3 - Division
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 3 
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId,
           'SP #3 - (@iA / @iB) = ' AS Operation,
           (@iA / @iB) AS Value;
END
GO

/************** The Test *****************/
--Run the first procedure
EXEC dbo.proc_ManipulateNumbers;1 @iA = 10, @iB = 2;
GO

--Run the second procedure
EXEC dbo.proc_ManipulateNumbers;2 @iA = 10, @iB = 2;
GO

--Run the third procedure
EXEC dbo.proc_ManipulateNumbers;3 @iA = 10, @iB = 2;
GO

--Check the ObjectId of the procedure
SELECT OBJECT_ID('dbo.proc_ManipulateNumbers','P') AS ProcedureObjectId;
GO

If you run the script provided above, this is what you would find as the output:

The output tells us that we have three stored procedures with the same schema, same name, same ObjectId, but different functionality – one adds two numbers whereas the others either multiply or divide them.

(If you are getting a headache by looking at the output, a sip of coffee might help)

The question therefore is:

How is it possible to have multiple stored procedures with different logic, but the same name?

How can the same ObjectId be assigned to all these mysterious procedures?

Numbered Stored Procedures – The Answer

The answer resides in a little known feature of SQL Server – numbered stored procedures.

Notice the use of semi-colon (;) followed by a number in the CREATE PROCEDURE statement. The semi-colon is not used as a statement terminator here, but is used to indicate that the definition is actually a different version of a stored procedure with the same name and should be grouped together.

When attempting to access these procedures, all one needs to do is again use the semi-colon (;) followed by the stored procedure version number. Hence, when we execute the following, SQL Server knows to use version #2, i.e. the one where supplied inputs are multiplied.

--Run the second procedure
EXEC dbo.proc_ManipulateNumbers;2 @iA = 10, @iB = 2;
GO

Because they share the same objectId, they can be dropped with a single DROP PROCEDURE statement.

WARNING!

Per Books-On-Line, “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Hence, while as interesting as this feature may sound, please avoid using this feature in new development work. Also, as versions move along, start moving away from this implementation.

Further Reading:

• Semi-colon as a statement terminator [Link]
• CREATE PROCEDURE statement [Link]

I had never heard of numbered stored procedure before encountering the mentioned Ask post. Had you heard about this in the past? Have you ever used numbered stored procedures? Why? Do let me know.

Until we meet next time,

Be courteous. Drive responsibly.