Sql Server: Why Would I Add ";1" To The End Of A Stored Procedure Name?

i came across a compatibility issue today, as a customer upgraded from Windows XP to Windows 7. The (12 year old code) is calling a stored procedure on the SQL Server called ai_ne

Solution 1:

see CREATE PROCEDURE (Transact-SQL) SQL Server 2008 documentation

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name            [ ; number ]  <<<<<<
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

;number

An optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Note:

This feature will be removed in a future version of Microsoft SQL Server.
    Avoid usingthis feature in new development work, and plan to
    modify applications that currently use this feature.

Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

you can use this system view to find all of these and begin to rewrite them as separate procedures:

sys.numbered_procedures (Transact-SQL)

Solution 2:

• why were we appending ;1 to the end of the stored procedure name? (what does it accomplish)

The ;1 means that you are calling Numbered Stored Procedures. You can have InsertOrders;1, InsertOrders;2, InsertOrders;3 as different versions with the same name. When you do a DROP on the InsertOrders, all numbered versions are dropped. This was a poor man's implementation of overloading.

• why was the SQL Server driver ignoring it?

The old SQL Server driver either knew what a numbered proc was, or was not smart enough to parse and compile that portion of code.

• why was a breaking change made in Windows 7?

• is the breaking compatibility change documented?

This will not be supported in a future version, but R2 supports numbered stored procs. I have personally never put numbered procs in production - only played with them, said "oh cool" and moved on.

Solution 3:

I had the same problem, until I added some code, to remove the ";1" if it was still at the end of the StoredProcName:

strProcName := StoredProc.StoredProcName;

IF (length(strProcName) > 2) AND (copy(strProcName, length(strProcName) - 1, 2) = ';1') THEN

  BEGIN

    delete(strProcName, length(strProcName) - 1, 2);

    StoredProc.StoredProcName := strProcName;

  END {IF};

StoredProc.Prepare;

StoredProc.ParamByName('@cntid').AsInteger := nCounterID;

StoredProc.ParamByName('@range').AsInteger := nRange;

StoredProc.ExecProc;

result := StoredProc.ParamByName('@Status').AsInteger;