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 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.
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.
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,