CODEWITHSUNDEEP
sqlsql-serversql-server-2005stored-procedures
ztatic
ztatic

Reputation: 1181

creating a stored procedure from a recursive query

I'd like to create a mssql stored procedure to run a query like the following:

SELECT thingID FROM things WHERE thingParentID = #arguments.id#

recursively, accumulating thingIDs in a list which is then returned by the stored procedure.

Does anyone know of a example like this that they can link to? or some documentation that might help me?

Thanks.

Upvotes: 2

Views: 2567

Answers (1)

Anthony Faull
Anthony Faull

Reputation: 17957

This will work on SQL Server 2005 and up.

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

Upvotes: 7

Related Questions