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 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

• sql-server
• t-sql
• cursor

• 3 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 Commented Mar 17 at 18:06
• 3 @Brad pretty sure that's the error, well spotted. Second error is using cursors – siggemannen Commented Mar 17 at 18:08
• 3 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 Commented Mar 17 at 18:10
• 2 @OP, you need to move the cursor declaration inside the while loop after @v_cur_rule_id has been fetched to. – siggemannen Commented Mar 17 at 18:35
• 2 This should almost certainly be written as a JOIN or APPLY instead of a nested cursor in the first place. – Joel Coehoorn Commented Mar 17 at 20:16

1 Answer 1

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;

best regards
Altin