Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
We're not using this feature, but I wonder if that's not more trouble than it's worth and just, as you say, a curiousity – jcolebrand Jan 7 '11 at 16:16
This feature has been deprecated and will be dropped in a future release. Use it carefully. – mrdenny Jan 8 '11 at 0:43
up vote 5 down vote accepted

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.

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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