sql-server-2005 - MS SQL Server 2005 sp_stored_procedures

Question

我目前正在运行

sp_stored_procedures

用于检索给定数据源的 SP 列表的存储过程。我需要通过它的 PROCEDURE_NAME 和 PROCEDURE_OWNER 列来查询这个数据集。然而，PROCEDURE_NAME 似乎有一个奇怪的返回值。好像是在名字的末尾附加了一个分号和一个数字。

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

这里是 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

最初我认为这些数字可能代表参数的数量(直到 NUM_INPUT_PARAMS 在 SQL Server 中实现)，但我添加和删除了参数并且没有看到列表中的变化。

这些数字是什么意思？

Answer 1

你可以有不同的同名存储过程，用数字区分，它们可以一次放在一起。语法是 still valid从 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.

该功能在 official deprecated list 上.

顺便说一句，您可以使用目录 View 来检索程序:sys.procedures .我发现使用 View 比使用过程容易得多。