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.