79515886

Date: 2025-03-17 22:35:49
Score: 2
Natty:
Report link

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

Reasons:
  • Blacklisted phrase (0.5): best regards
  • Blacklisted phrase (1): regards
  • Long answer (-1):
  • Has code block (-0.5):
  • User mentioned (1): @Brad
  • User mentioned (0): @siggemannen
  • User mentioned (0): @v_cur_rule_id
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: altink