我想创建一个mssql存储过程来运行如下查询:SELECT thingIDFROM thingsWHERE thingParentID = #arguments.id#
递归地将thingID
累积在列表中,然后由存储过程返回。
有人知道可以链接到这样的示例吗?或一些可能对我有帮助的文档?
谢谢。
最佳答案
这将适用于SQL Server 2005及更高版本。
CREATE FUNCTION dbo.Ancestors (@thingID int)
RETURNS TABLE
AS
RETURN
WITH CTE AS
(
SELECT thingID, 1 [Level]
FROM dbo.things
WHERE thingParentID = @thingID
UNION ALL
SELECT p.thingID, [Level] + 1 [Level]
FROM CTE c
JOIN dbo.things p
ON p.thingParentID = c.thingID
)
SELECT thingID, [Level]
FROM CTE
GO
CREATE PROCEDURE GetAncestors (@thingID int)
AS
SELECT thingID, [Level]
FROM dbo.Ancestors(@thingID)
GO