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:
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:
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,
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.
LikeLike
@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).
LikeLike
Nakul, This is my post which gives some more information on the same [http://beyondrelational.com/modules/2/blogs/70/posts/19623/numbered-stored-procedures-in-sql-server.aspx][1]
[1]: http://beyondrelational.com/modules/2/blogs/70/posts/19623/numbered-stored-procedures-in-sql-server.aspx
LikeLike
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.
LikeLike