CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2005
Pawan Kumar
Pawan Kumar

Reputation: 522

Stored Procedure using Recursive Query

I am writing a stored Procedure which should return me result and the same result will be passed as input to the stored Procedure and the recursion should stop after the input and output are same at any particular time.

Below is the stored Procedure/function and the Run Command (New to Stored Procedure).

CREATE FUNCTION dbo.Ancestors (@List VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
WITH CTE AS
(
 SELECT DISTINCT Child AS RESULT FROM example WHERE Parent IN( SELECT Id = 
 Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM 
 dbo.SplitInts(@List, ','))
 UNION ALL
 SELECT DISTINCT Parent AS RESULT FROM example WHERE Parent IN( SELECT Id = 
 Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM 
 dbo.SplitInts(@List, ','))
 )
SELECT RESULT FROM CTE
UNION
SELECT RESULT FROM CTE
GO

CREATE PROCEDURE GetAncestors (@thingID VARCHAR(MAX))
AS
SELECT RESULT FROM dbo.Ancestors(@thingID)
GO

EXEC GetAncestors @thingID = '100'

Result is - '100,101' 

EXEC GetAncestors @thingID = '100,101'

Result is - '100,101,102'

EXEC GetAncestors @thingID = '100,101,102'

Result is - '100,101,102'

What i am actually looking for it is to give the result at one shot by passing 100 or 101 or 102

Result should be - '100,101,102'

Below is the Example Table:

|     Parent          |     Child        |
|---------------------|------------------|
|      100            |      101         |
|---------------------|------------------|
|      101            |      102         |
|---------------------|------------------|

Upvotes: 4

Views: 17428

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

It looks like you need a simple recursive query that traverses the tree down starting from a given node. The CTE returns a list of pairs (Parent, Child), so I unioned them together to get a list of individual nodes (and remove duplicates along the way).

Note, that the query is recursive. The CTE (common table expression) references itself.

Sample data

I added few more rows to actually see what is going on.

DECLARE @T TABLE (Parent int, Child int);

INSERT INTO @T VALUES
(100, 101),
(101, 102),
(102, 103),
(103, 104),
(101, 108),
(108, 109),
(208, 209),
(209, 210);

Query

WITH
CTE
AS
(
    SELECT
        Parent, Child
    FROM @T
    WHERE Parent = 100

    UNION ALL

    SELECT
        T.Parent, T.Child
    FROM
        CTE
        INNER JOIN @T AS T ON T.Parent = CTE.Child
)
SELECT
    Parent AS Result
FROM CTE

UNION

SELECT
    Child AS Result
FROM CTE
;

Result

Result
100
101
102
103
104
108
109

You can put the query in the stored procedure like this:

CREATE PROCEDURE GetAncestors(@thingID int)
AS
BEGIN
    SET NOCOUNT ON;

    WITH
    CTE
    AS
    (
        SELECT
            Example.Parent, Example.Child
        FROM Example
        WHERE Parent = @thingID

        UNION ALL

        SELECT
            Example.Parent, Example.Child
        FROM
            CTE
            INNER JOIN Example ON Example.Parent = CTE.Child
    )
    SELECT
        Parent AS Result
    FROM CTE

    UNION

    SELECT
        Child AS Result
    FROM CTE
    ;

END
GO

Upvotes: 2

Related Questions