MS SQL Server 2005 sp_stored_procedures

Question

I'm currently running the

sp_stored_procedures

stored procedure to retrieve a listing of SP for a given data source. I will need to query this dataset by it's PROCEDURE_NAME and PROCEDURE_OWNER columns. However, PROCEDURE_NAME seems to have a strange return value. It seems to append a semicolon and a number to the end of the name.

sel_AppUser;1
sel_AppUser_all;1
sel_AppUser_by_login;1
sel_Browsable;1

Here's sel_Browsable

CREATE PROCEDURE [dbo].[sel_Browsable]
@BrowsableID uniqueidentifier
AS
BEGIN
SELECT * FROM Browsable WHERE browsableID = @BrowsableID FOR XML AUTO, ELEMENTS
SELECT * FROM Attribute WHERE objectID = @BrowsableID FOR XML AUTO, ELEMENTS
SELECT * FROM Search WHERE browsableID = @BrowsableID ORDER BY searchOrder FOR XML AUTO, ELEMENTS
SELECT Attribute.* FROM Attribute INNER JOIN Search ON Attribute.objectID = Search.searchID WHERE browsableID = @BrowsableID ORDER BY searchOrder FOR XML AUTO, ELEMENTS
SELECT Criterion.* FROM Criterion INNER JOIN Search ON Criterion.searchID = Search.searchID WHERE browsableID = @BrowsableID ORDER BY searchOrder, criterionOrder FOR XML AUTO, ELEMENTS
SELECT Attribute.* FROM Attribute INNER JOIN Criterion ON Attribute.objectID = Criterion.criterionID INNER JOIN Search ON Criterion.searchID = Search.searchID WHERE browsableID = @BrowsableID ORDER BY searchOrder, criterionOrder FOR XML AUTO, ELEMENTS
SELECT CriterionOperator.* FROM CriterionOperator INNER JOIN Criterion ON CriterionOperator.criterionID = Criterion.criterionID INNER JOIN Search ON Criterion.searchID = Search.searchID WHERE browsableID = @BrowsableID ORDER BY searchOrder, criterionOrder, operatorOrder FOR XML AUTO, ELEMENTS
SELECT CriterionValue.* FROM CriterionValue INNER JOIN Criterion ON CriterionValue.criterionID = Criterion.criterionID INNER JOIN Search ON Criterion.searchID = Search.searchID WHERE browsableID = @BrowsableID ORDER BY searchOrder, criterionOrder, criterionValueOrder FOR XML AUTO, ELEMENTS
END

Initially I thought that these numbers might represent the number of parameters (until NUM_INPUT_PARAMS is implemented in SQL Server) but I've added and removed parameters and haven't seen a change in the listing.

What do these numbers mean?

Answer 1

See the MSDN documentation for CREATE PROCEDURE. Under the "Arguments" section, for procedure_name, they say that adding that sort of number is deprecated. From the syntax diagram, the ;1 is not part of the procedure name, and is optional. Since the ; character isn't legal in T-SQL identifiers, you can just look at the string before ; in the query results.

Answer 2

Well I don't know what they mean - it could be the version?

But I'd recommend an alternative to sp_stored_procedures:

select specific_schema, specific_name, * from information_schema.routines

It gives you the name and owner (among other things), and it doesn't have strange characters.

Answer 3

You can have different stored procedures with the same name, differentiated by numbers, wich can be dropped together at once. The syntax is still valid as of SQL 2k8:

; number Is 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. For example, an application called orders might use procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the whole group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around only procedure_name.

The feature is on the official deprecated list.

BTW you can use the catalog views to retrieve the procedures: sys.procedures. I find using the views much easier than using the procedures.