Help to convert cursor to CTE

Question

ALTER PROCEDURE [dbo].[MDC_COST_CODE_LEVEL8_PROC] (@contextid INT) AS
BEGIN
declare @ccid int, @lev1 int, @lev2 int, @lev3 int, @lev4 int, @lev5 int, @lev6 int, @lev7 int, @lev8 int, @count int
--lev1 -- >Cost Codes have no root element
declare cur_level cursor scroll for
SELECT ID, MDC_COST_CODE_LEVEL1_FK, MDC_COST_CODE_LEVEL2_FK, MDC_COST_CODE_LEVEL3_FK, MDC_COST_CODE_LEVEL4_FK,
MDC_COST_CODE_LEVEL5_FK, MDC_COST_CODE_LEVEL6_FK, MDC_COST_CODE_LEVEL7_FK, MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE
WHERE MDC_CONTEXT_FK = @contextid AND MDC_COST_CODE_PARENT_FK IS NULL AND MDC_COST_CODE_LEVEL1_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @ccid, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev2
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL1_FK AND a.MDC_COST_CODE_LEVEL2_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @ccid, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev1 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev3
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL2_FK AND a.MDC_COST_CODE_LEVEL3_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @ccid, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev2 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev4
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL3_FK AND a.MDC_COST_CODE_LEVEL4_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @ccid,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev3 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev5
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL4_FK AND a.MDC_COST_CODE_LEVEL5_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @ccid, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev4 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev6
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL5_FK AND a.MDC_COST_CODE_LEVEL6_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @ccid, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev5 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev7
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL6_FK AND a.MDC_COST_CODE_LEVEL7_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @ccid, MDC_COST_CODE_LEVEL8_FK = @lev8
WHERE MDC_COST_CODE_PARENT_FK = @lev6 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev8
declare cur_level cursor scroll for
SELECT a.ID, b.MDC_COST_CODE_LEVEL1_FK, b.MDC_COST_CODE_LEVEL2_FK, b.MDC_COST_CODE_LEVEL3_FK, b.MDC_COST_CODE_LEVEL4_FK,
b.MDC_COST_CODE_LEVEL5_FK, b.MDC_COST_CODE_LEVEL6_FK, b.MDC_COST_CODE_LEVEL7_FK, b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a, MDC_COST_CODE b
WHERE a.MDC_CONTEXT_FK = @contextid AND a.MDC_COST_CODE_PARENT_FK = b.ID
AND a.MDC_COST_CODE_PARENT_FK = b.MDC_COST_CODE_LEVEL7_FK AND a.MDC_COST_CODE_LEVEL8_FK IS NULL;
open cur_level
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
while(@@Fetch _status = 0)
begin
UPDATE MDC_COST_CODE SET MDC_COST_CODE_LEVEL1_FK = @lev1, MDC_COST_CODE_LEVEL2_FK = @lev2, MDC_COST_CODE_LEVEL3_FK = @lev3, MDC_COST_CODE_LEVEL4_FK = @lev4 ,
MDC_COST_CODE_LEVEL5_FK = @lev5, MDC_COST_CODE_LEVEL6_FK = @lev6, MDC_COST_CODE_LEVEL7_FK = @lev7, MDC_COST_CODE_LEVEL8_FK = @ccid
WHERE MDC_COST_CODE_PARENT_FK = @lev7 AND ID = @ccid;
fetch next FROM cur_level into @ccid, @lev1, @lev2, @lev3, @lev4 , @lev5, @lev6, @lev7, @lev8
end
close cur_level
Deallocate cur_level
--lev8+
WHILE (SELECT COUNT(*) FROM MDC_COST_CODE
WHERE MDC_CONTEXT_FK = @contextid AND MDC_COST_CODE_LEVEL1_FK IS NULL AND MDC_COST_CODE_LEVEL2_FK IS NULL AND MDC_COST_CODE_LEVEL3_FK IS NULL AND MDC_COST_CODE_LEVEL4_FK IS NULL
AND MDC_COST_CODE_LEVEL5_FK IS NULL AND MDC_COST_CODE_LEVEL6_FK IS NULL AND MDC_COST_CODE_LEVEL7_FK IS NULL AND MDC_COST_CODE_LEVEL8_FK IS NULL
) <> 0
BEGIN
UPDATE a SET a.MDC_COST_CODE_LEVEL1_FK = b.MDC_COST_CODE_LEVEL1_FK, a.MDC_COST_CODE_LEVEL2_FK = b.MDC_COST_CODE_LEVEL2_FK, a.MDC_COST_CODE_LEVEL3_FK = b.MDC_COST_CODE_LEVEL3_FK, a.MDC_COST_CODE_LEVEL4_FK = b.MDC_COST_CODE_LEVEL4_FK,
a.MDC_COST_CODE_LEVEL5_FK = b.MDC_COST_CODE_LEVEL5_FK, a.MDC_COST_CODE_LEVEL6_FK = b.MDC_COST_CODE_LEVEL6_FK, a.MDC_COST_CODE_LEVEL7_FK = b.MDC_COST_CODE_LEVEL7_FK, a.MDC_COST_CODE_LEVEL8_FK = b.MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE a
INNER JOIN (SELECT ID, MDC_COST_CODE_LEVEL1_FK, MDC_COST_CODE_LEVEL2_FK, MDC_COST_CODE_LEVEL3_FK, MDC_COST_CODE_LEVEL4_FK,
MDC_COST_CODE_LEVEL5_FK, MDC_COST_CODE_LEVEL6_FK, MDC_COST_CODE_LEVEL7_FK, MDC_COST_CODE_LEVEL8_FK
FROM MDC_COST_CODE) b
ON a.MDC_COST_CODE_PARENT_FK = b.ID AND a.MDC_CONTEXT_FK = @contextid
AND a.MDC_COST_CODE_LEVEL1_FK IS NULL AND a.MDC_COST_CODE_LEVEL2_FK IS NULL AND a.MDC_COST_CODE_LEVEL3_FK IS NULL AND a.MDC_COST_CODE_LEVEL4_FK IS NULL
AND a.MDC_COST_CODE_LEVEL5_FK IS NULL AND a.MDC_COST_CODE_LEVEL6_FK IS NULL AND a.MDC_COST_CODE_LEVEL7_FK IS NULL AND a.MDC_COST_CODE_LEVEL8_FK IS NULL
END
END