Versions of Stored Procedures

I never knew this, but stored procedures have versions.

I was browsing the CREATE PROCEDURE doc page, and stumbled upon this item. In the beginning part of a stored procedure definition, after the name, there is a section that starts with

; number

Hmmm, I read the entry and it says the number is an integer that is used to group procedures of the same name. There is a note this is a deprecated feature that may be dropped, should not be used for new work, and old work ought to be modified, but it was interesting, so I tried it.

First, create a proc:

CREATE PROCEDURE dbo.GetOne
AS
SELECT 1
GO

Once this is done, we can execute it and it works. Now, let’s modify this. I’ll use this code:

CREATE PROCEDURE GetOne;2
AS
SELECT 'One'
GO

You can see these will have different results. If I execute them, I do so with the name, including the optional integer.

 2019-01-24 17_58_15-SQLQuery8.sql - Plato_SQL2017.Tsql (PLATO_Steve (60))_ - Microsoft SQL Server Ma

In SSMS, there is only one object listed.

2019-01-24 17_59_44-SQLQuery8.sql - Plato_SQL2017.Tsql (PLATO_Steve (60))_ - Microsoft SQL Server Ma

If I drop the procedure, both are gone.

2019-01-24 18_00_18-SQLQuery8.sql - Plato_SQL2017.Tsql (PLATO_Steve (60))_ - Microsoft SQL Server Ma

I’m not completely sure where I’d use this feature, and I can see not investing in it, but I found this fascinating. All these years of writing stored procedures and I learned something new this week.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

4 Responses to Versions of Stored Procedures

  1. Hi Steve. Yeah, this is something that really should _not_ be used by anyone ever. There is absolutely no benefit in any conceivable way to using numbered stored procs; there is only pain. Besides not being able to see the different group numbers of the main proc name in SSMS (and I think there are other functional areas that do not support this construct), you also cannot drop numbered stored procedures except by dropping the main one, in which case it drops them all. Also, they can be numbered sparsely, with the only requirement being that the first one (i.e. un-numbered or “;1”) exists. Consider the following:

    CREATE PROCEDURE #NumberedProc (@Input INT)
    AS SELECT 1;
    GO
    CREATE PROCEDURE #NumberedProc;2 (@Input INT)
    AS SELECT 2;
    GO
    CREATE PROCEDURE #NumberedProc;367 (@Input INT)
    AS SELECT 3;
    GO

    DROP PROCEDURE #NumberedProc;2
    — Incorrect syntax near ‘;’.

    GO
    DROP PROCEDURE [#NumberedProc;2]
    — Cannot drop the procedure ‘#NumberedProc;2’, because it does not exist or you do not have permission.
    GO

    DROP PROCEDURE #NumberedProc; — this is the only way to delete the numbered versions

    Take care, Solomon..

  2. So… there is a use case that is immediately obvious but isn’t good.

    Attacker wants to build scripts and leave them on the SQL server where they remain pretty much undetectable… that way he doesn’t have to figure things back out when he visits again.

    This feature helps with that.

    • Chris Fair says:

      That’s right where I went, too. Well, really as a practical joke to a fellow developer. Create a slightly-off version of a proc first, then modify the second one slightly and use that as the main one in the code, but only the second one returns the correct results. It’s a real jerk move, to be sure, but it could be a funny practical joke if you have the time and the right personalities that you’re working with.

      Otherwise, yeah, it’s really only a big security nightmare.

  3. way0utwest says:

    I can’t think of a good reason do to this, especially as your calling code has to change and there isn’t any immediate benefit I see.

Leave a Reply

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.