Stored Procedure using Recursive Query

Question

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         |
|---------------------|------------------|

Most likely it could be done in one recursive query without calling stored procedures many times. Look up `Recursive Common Table Expression`. If you include a dozen rows with sample data and expected result in the question, you'll quickly get a good answer. — Vladimir Baranov, May 30 '18 at 13:49
Added the sample data...Please Provide the solution...thnx..expected result is already added in description of question — Pawan Kumar, May 30 '18 at 14:40

Vladimir Baranov · Answer 1 · 2018-05-30T23:21:24.793

2

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

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

edited May 30 '18 at 23:21

answered May 30 '18 at 14:54

Vladimir Baranov

31,799
5
53
90

Thanks for immediate response....Cannot we do it through stored Procedure...Since i need to invoke a stored procedure from express.js by passing the parameter '100' – Pawan Kumar May 30 '18 at 15:21
Added Recursive Common Query could you please check, i am not able to fetch at one shot – Pawan Kumar May 30 '18 at 16:18
Thanks a lot @Vladimir Baranov.. But even if i pass 102 or 101 or 100...I should get the result as 100,101,102.. The Scenario is Parent and child are related and the moment i pass 102, it should fetch me 101 and since 101 is related 100,, the o/p should be 100,101,102 – Pawan Kumar May 31 '18 at 06:38
@PawanKumar, it means that I wasted my time writing this answer. In the future try to provide all important details in the question from the very start. – Vladimir Baranov May 31 '18 at 11:15
Really Sorry for that...but after checking the other scenario, i missed one....Thanks for whatever help you have provided. – Pawan Kumar May 31 '18 at 12:15
@PawanKumar, have a look at a related and more general query: [How to find all connected subgraphs of an undirected graph](https://stackoverflow.com/q/35254260/4116017). In your case you don't need all subgraphs, but only one that starts at a given node. If I understood your requirements correctly. – Vladimir Baranov May 31 '18 at 12:38

Stored Procedure using Recursive Query

1 Answers1