The question refers to the number parameter in this msdn documentation

If you don't you can create multiple stored procedures in SQL-Server differentiated by number and drop them with a single drop.

create procedure dbo.stored_proc1 as select 1
create procedure dbo.stored_proc1;2 as select 2
exec stored_proc1
-- returns 1
exec stored_proc1;2
-- returns 2
drop stored_proc1
-- drops both 

I wonder if this feature is used by anybody for something useful or if it is just a historic curiosity.


2 Answers 2


I used this feature about 6 years ago. You are right, we cannot do this:

drop stored_proc1;2


exec stored_proc1 

the same as

exec stored_proc1;1

Why did we use it? We have a lot of algorithms (strategies) to calculate data, so we can easily switch between versions without changing the call interface.


Numbered stored procedures are deprecated.

Numbered procedures are deprecated. Use of numbered procedures is discouraged. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled.

My team encountered this in a maintenance project. We couldn't figure it out at first. Then, we did some research and found out that it is deprecated. We had to rebuild it to normal stored procs.

Numbered stored procedures won't show up in SSMS's Object Explorer tree.

Numbered Stored Procedures

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.