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.
In SSMS, there is only one object listed.
If I drop the procedure, both are gone.
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.
Voice of the DBA 
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..
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike