Zainab JH Zainab JH - 4 months ago 8
SQL Question

Define SQL variable inside the retrieving query

I have a recursive (using CTE) query to get the top level parent of a certain child/grandchild. And I have a big view that contains the id of the child/grandchild. Here is my recursive query:

DECLARE @childID INT
SET @childID = 16579; --child to search

WITH RCTE AS
(
SELECT *, 1 AS Lvl, Company.CompanyID As Child FROM Company
WHERE CompanyID = @childID

UNION ALL

SELECT rh.*, Lvl+1 AS Lvl, rh.CompanyID As Child FROM Company rh
INNER JOIN RCTE rc ON rh.CompanyID = rc.ParentID
)
SELECT TOP 1 p.CompanyID, p.CompanyName, r.Child
FROM RCTE r
inner JOIN Company p ON p.CompanyID= r.ParentID
ORDER BY lvl DESC


Is there a way of setting the
@childID
inside the view to retrieve the grand parent/parent (what ever the top level parent is) of all records in one query without the need to run the recursive function for each row of the view separately?

Answer

If you include the base child ID as part of the CTE and carry it down through the iterations in the recursion then the SQL engine should be smart enough to limit it appropriately and you should get performance just as good as you would with the variable. I just did some quick tests to confirm.

An example:

CREATE VIEW Company_Hierarcy
AS
    WITH RCTE AS
    (
        SELECT
            C.Name, -- etc., since we never actually use SELECT *
            C.CompanyID,
            C.ParentID,
            1 AS Lvl,
            C.CompanyID AS BaseID
        FROM
            dbo.Company C

        UNION ALL

        SELECT
            C2.Name,
            C2.CompanyID,
            CTE.Lvl+1 AS Lvl,
            CTE.BaseID AS BaseID
        FROM
            RCTE CTE
        INNER JOIN Company C2 ON C2.CompanyID = CTE.ParentID
    )
    SELECT
        R.Name,
        R.CompanyID,
        R.ParentID,
        R.lvl,
        R.BaseID
    FROM
        RCTE R

You can then check the performance of:

SELECT * FROM Company_Hierarcy

versus:

SELECT * FROM Company_Hierarcy WHERE BaseID = 16579