sql-servert-sqlcursor

Nested SQL Server cursor has no records


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


Solution

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