SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...