#0289 – SQL Server – Deprecated features – Numbered Stored Procedures


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:

image

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.

Advertisements

4 thoughts on “#0289 – SQL Server – Deprecated features – Numbered Stored Procedures

  1. johan.parlevliet

    I have seen this on a sybase system. It was in a database of a software supplier.

    – create procedure proc1;1 @__p1 float as select field1, field2 from xxx where id = @__p1
    – create procedure proc2;2 @__p1 float as select field1 from xxx where id = @__p1

    I have seen it used with up to +/- 50 version numbers. It were select and delete statements on different tables.

    I do not know why the supplier/programmer did this. The names of the stored procedures where also numbers so code would not be very good readable.

    Like

    Reply
  2. Nakul Vachhrajani

    @johan: Thank-you for sharing your experience. It makes sense that this method of “overloading” (for lack of a better term) came over to Microsoft SQL Server from Sybase.

    Not only would the code be unreadable, it can end up causing manageability issues as well. I cannot imagine the confusion a group of 50 stored procedures with the same name would have caused from a manageability perspective. I am sure your experience in working with such a system would have helped you a lot in the future (at least from a perspective of what one should not do).

    Like

    Reply
  3. Nakul Vachhrajani

    Thank-you, Madhivanan for extending the post. It was really fun learning about this feature when writing my blog and exploring some of the finer aspects with your post.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s