Numbered Stored Procedure

I was reading Msdn article on contained databases and found out that one of the restrictions on contained databases “database cannot have Numbered Stored Procedures”.

I honestly never heard of “Numbered Stored Procedures” and quick a Google search came up with couple of articles including this Msdn article. It was quite surprising to know something like existed for a while and I had no idea about. Microsoft does not recommend using feature and this is going to be deprecated in future versions(and rightly so,i believe)

Basically, Numbered Procedures are similar to regular stored procedures but they can be grouped together with ‘;’ followed by number.
The idea of this is so that similar procedures with slight variation of code can be combined.

create procedure usp_cdcproc(@a int)
as
begin
Select 'This is Numbered Stored Procedure '+try_convert(varchar(1),@a)
end
go
create procedure usp_cdcproc;2(@a int)
as
begin
Select 'This is Numbered Stored Procedure '+try_convert(varchar(2),@a)
end
go
create procedure usp_cdcproc;3(@a varchar(2))
as
begin
Select 'This is Numbered Stored Procedure '+@a
end

Select name,Object_ID,type_desc from sys.procedures
ProcCreation

Select * from sys.numbered_procedures
NumberedProc

Select object_name(object_Id) as [Procedure Name],* from sys.numbered_procedure_parameters
ProcParameters

While I do not know how prominently they are used(I do not think they are) but apparently we can create group of stored procedures but we cannot drop them individually. The entire group will be dropped.

Advertisement