Nested SQL Server cursor has no records

Question

I have a master detail schema with two tables. I am using a cursor within another cursor to loop them both.

BEGIN

--set NOCOUNT ON;

DECLARE Cur_Rule CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
    SELECT rule_id 
    FROM OMEGACA.ACC_POL_RULE 
    WHERE rule_id IN (3, 6) 
    ORDER BY rule_id;

DECLARE @v_cur_rule_id  int;

DECLARE Cur_Cond CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
    SELECT cond_id 
    FROM OMEGACA.ACC_POL_COND 
    WHERE rule_id = @v_cur_rule_id 
    ORDER BY cond_id;

DECLARE @v_cur_cond_id  int;

-- BEGIN LOOP C_RULE
OPEN Cur_Rule;
FETCH NEXT FROM Cur_Rule INTO @v_cur_rule_id;

WHILE @@FETCH_STATUS = 0 
BEGIN
    PRINT ('Rule:' + CONVERT(NVARCHAR(10), @v_cur_rule_id));

    -- BEGIN LOOP C_COND
    OPEN Cur_Cond;

    FETCH NEXT FROM Cur_Cond INTO @v_cur_cond_id;

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        PRINT ('Cond:' + CONVERT(NVARCHAR(10), @v_cur_cond_id));
        FETCH NEXT FROM Cur_Cond INTO @v_cur_cond_id;           
    END;

    CLOSE Cur_Cond;
    --DEALLOCATE Cur_Cond;
    -- END LOOP C_COND

    FETCH NEXT FROM Cur_Rule INTO @v_cur_rule_id;
END;

CLOSE Cur_Rule;
DEALLOCATE Cur_Cond;
DEALLOCATE Cur_Rule;
-- END LOOP C_RULE

END;

In table acc_pol_cond there are records with rule_id 3 and 6.

SELECT cond_id, rule_id
FROM [OmegaCoreAudit].[OMEGACA].[ACC_POL_COND]
WHERE rule_id IN (3, 6)

cond_id rule_id
1   3
4   3
5   6
6   6
21  3
22  6
23  3
24  6
25  3
26  6
27  6
28  6
30  3
31  3

Problem:
The inside cursor Cur_Cond has no rows.

I get printed only:
Rule:3
Rule:6
... and not Cond:Id lines

I have not used cursors in a LONG time but it may be that you are declaring the cursor at the top using the variable @v_cur)rule_id but at the time you declare it, the variable is empty. You may need to declare it inside the other cursor. Though it seems you can just use a JOIN or a loop without a cursor at all. — Brad
– Brad, Commented Mar 17 at 18:06
@Brad pretty sure that's the error, well spotted. Second error is using cursors — siggemannen
– siggemannen, Commented Mar 17 at 18:08
I agree that the use of cursors is likely an XY Problem. Admittedly though, the real question should likely be asked as a new question rather than an edit, as it'll probably completely change the question. — Thom A
– Thom A ♦, Commented Mar 17 at 18:10
@OP, you need to move the cursor declaration inside the while loop after @v_cur_rule_id has been fetched to. — siggemannen
– siggemannen, Commented Mar 17 at 18:35
This should almost certainly be written as a JOIN or APPLY instead of a nested cursor in the first place. — Joel Coehoorn
– Joel Coehoorn, Commented Mar 17 at 20:16

Dale K · Accepted Answer · 2025-04-24 19:39:48Z

0

The answer is given in the comments by. @Brad and @siggemannen

Must DECLARE (and not just OPEN) the Detail cursor Cur_Cond where its where-clause @v_cur_rule_id has been set (for each row) by the Master cursor Cur_Rule.

Solution Code:

BEGIN

--set NOCOUNT ON;

declare Cur_Rule CURSOR LOCAL READ_ONLY FORWARD_ONLY for
    select rule_id from OMEGACA.ACC_POL_RULE where rule_id in (3,6) order by rule_id;
declare @v_cur_rule_id  int;

declare @v_cur_cond_id  int;

-- BEGIN LOOP C_RULE
OPEN Cur_Rule;
fetch next from Cur_Rule into @v_cur_rule_id;

while @@FETCH_STATUS = 0 
BEGIN
PRINT ('Rule:' + CONVERT(NVARCHAR(10), @v_cur_rule_id));

declare Cur_Cond CURSOR LOCAL READ_ONLY FORWARD_ONLY for
    select cond_id from OMEGACA.ACC_POL_COND where rule_id = @v_cur_rule_id order by cond_id;

    -- BEGIN LOOP C_COND
    OPEN Cur_Cond;
    fetch next from Cur_Cond into @v_cur_cond_id;
        while @@FETCH_STATUS = 0 
        BEGIN
        PRINT ('Cond:' + CONVERT(NVARCHAR(10), @v_cur_cond_id));
        fetch next from Cur_Cond into @v_cur_cond_id;
        END;
    CLOSE Cur_Cond;
    DEALLOCATE Cur_Cond;
    -- END LOOP C_COND

fetch next from Cur_Rule into @v_cur_rule_id;
END;

CLOSE Cur_Rule;
DEALLOCATE Cur_Rule;
-- END LOOP C_RULE

END;

edited Apr 24 at 19:39

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Mar 17 at 22:30

altink

3751 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dale K Apr 24 at 19:40

You know you can self accept?

Collectives™ on Stack Overflow

Nested SQL Server cursor has no records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related