SQL Server: Run separate CTE for each record without cursor or function

Question

Given table A with a column LocationID and many records,

Is it possible to fully run a CTE for each record, without using a cursor (while fetch loop) or function (via cross apply)?

I can't run a CTE from the table A because the CTE will go deep through a parent-child hierarchical table (ParentID, ChildID) to find all descendants of a specific type, for each LocationID of table A. It seems that if I do CTE using table A, it will mix the children of all LocationID in table A.

Basically I need to separately run a CTE, for each LocationID of table A, and put in a table with LocationID and ChildID columns (LocationID are the ones from table A and ChildID are all descendants of a specific type found via CTE).

No idea what you're trying to ask based on this vague description. A CTE isn't something you "run", it's a derived table, so I think this is probably a XY question. — Tab Alleman, Oct 29 '18 at 19:41
@TabAlleman Sorry if it wasn't clear enough. I was able to find a solution and posted it. — igorjrr, Oct 29 '18 at 21:23

score 1 · Answer 1 · answered Oct 29 '18 at 19:35

1

This is your basic layout.

;with CTE AS
(
   select .......
)

select *
from CTE
cross apply (select distinct Location from TableA) a
where CTE.Location=a.Location

Some sample data and expected results will provide for a better answer.

answered Oct 29 '18 at 19:35

KeithL

5,348
3
19
25

score 0 · Answer 2 · answered Oct 29 '18 at 19:48

You could do something like this:

Declare @LocationID As Int
Select
    LocationID
    , 0 as Processed
Into #Temp_Table
From TableA

While Exists (Select Top 1 1 From #Temp_Table Where Processed = 0)
Begin
    Select Top 1 @LocationID = LocationID
    From #Temp_Table 
    Where Processed = 0
    Order By LocationID

    /*  Do your processing here */

    Update #Temp_Table Set
        Processed = 0
    Where LocationID = @LocationID
End

It's still RBAR but (in my environment, at least) it's way faster than a cursor.

score 0 · Accepted Answer · answered Oct 29 '18 at 21:21

I was able to find a solution. I just had to keep the original LocationID as a reference, then in the CTE results, which will include all possible records as it goes deep into the list, I apply the filter I need. Yes, all records are mixed in the results, however because the reference to origin table's LocationID was kept (as OriginalParentID) I'm still able to retrieve it.

;WITH CTE AS
(
    --Original list of parents
    SELECT a.LocationID AS OriginalParentID, l.ParentID, l.ChildID, l.ChildType
    FROM TableA a
    INNER JOIN tblLocationHierarchy l ON l.ParentID = a.LocationID

    UNION ALL

    --Getting all descendants of the original list of parents
    SELECT CTE.OriginalParentID, l.ParentID, l.ChildID, l.ChildType
    FROM tblLocationHierarchy l
    INNER JOIN CTE ON CTE.ChildID = l.ParentID
)
SELECT OriginalParentID, ChildID
FROM CTE
--Filtering is done here
WHERE ChildType = ...

SQL Server: Run separate CTE for each record without cursor or function

3 Answers3