How to use a WITH AS table in a cursor loop to start a stored procedure

Question

How to get the result in WITH table ASa loop CURSOR? I previously asked how to get recursive results from my table

How to read all records recursively and show by TSQL level

;with C as
(
    definition ...
)

I created a CURSOR loop where I want to run a specific stored procedure for all the results in table

declare @id int, @parent int
declare cur cursor local fast_forward 
for 
    select id, parent from C
open cur
fetch next from cur into @id, @parent
while @@fetch_status = 0
    begin
    exec storedProcedure @id=@id, @parent=@parent
fetch next from cur into @id, @parent
end
close cur
deallocate cur

The problem is that CURSOR does not know tablefrom the WITH AS result.

Invalid object name 'C'.

Answer 1

You can create a temporary table or table variable to hold the rows that you return with the CTE query, and then you use this table as the source for your cursor.

declare @T table
(
  id int,
  parent int
)

;with C as
(
  select 1 as id, 2 as parent
)
insert into @T
select id, parent
from C

declare cur cursor for select id, parent from @T