11

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
go
create procedure dbo.stored_proc1;2 as select 2
go
exec stored_proc1
-- returns 1
go
exec stored_proc1;2
-- returns 2
go
drop stored_proc1
-- drops both 
go

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

  • 2
    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
  • 2
    This feature has been deprecated and will be dropped in a future release. Use it carefully. – mrdenny Jan 8 '11 at 0:43
5

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

drop stored_proc1;2

and

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.

7

Numbered stored procedures are deprecated.

msdn
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, privacy policy and cookie policy

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