SSMS 2012 inconsistently handles numbered procedures - by AaronBertrand

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 764197 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 9/22/2012 1:40:33 PM
Access Restriction Public


In Management Studio 2012, attempting to modify a numbered procedure only yields the first numbered procedure, rather than all versions. Create the following numbered procedure:


Now different functionality within SSMS treats this "single object" differently:

 - Right-click procedure > Modify only shows the first version
 - Right-click procedure > Script as alter only shows the first version
 - Right-click procedure > Script as Create shows both
 - Right-click procedure > Script as Drop and Create shows both
 - Right-click database > Tasks > Generate Scripts shows both

It is also inconsistent within metadata queries (though I will admit that much of this may be by design):

 - sp_helptext shows only the first version
 - sys.sql_modules shows only the first version
 - INFORMATION_SCHEMA.ROUTINES shows only the first version
 - OBJECT_DEFINITION shows only the first version
 - syscomments shows both
 - sys.numbered_procedures shows only the second version

I know about this bug, which actually caused an error when trying to Modify a numbered procedure, and which was fixed in SQL Server 2008:

I realize that numbered procedures are deprecated, but while they continue to be supported, the tooling should be consistent in how they are handled. Users will have to manage their existing numbered procedures from the newest version of the tool, even if they are in the lowest version supported by the tool, long after the feature is actually discontinued in the engine.
Sign in to post a comment.
Posted by AaronBertrand on 4/25/2013 at 9:18 AM
Closed as fixed? When? Where? How? More comments please?
Posted by mobe67 on 2/14/2013 at 4:03 AM
I'm having this problem too. Will be solved ? Does this way of work storeds, using numbered storeds, will be non supported in future versions ?? Must we change all our numbered storeds to unique name storeds ? Thanks.
Posted by Microsoft on 9/25/2012 at 2:49 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.