Bug #61917 Unable to save result of a sum into a local variable in a stored proc

• Submitted: 19 Jul 2011 19:41
• Modified: 19 Jul 2011 20:19
• Reporter: Prashant Doshi
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.5.13-enterprise-commercial-advanced-lo
• OS: Windows
• CPU Architecture: Any

[19 Jul 2011 19:41] Prashant Doshi

Description:
Structure of the stored procedure is as follows:

BEGIN

Declare local variables ..

declare l_program_fee double(10,2);
declare l_last_row int;
...

Declare cursor ...

DECLARE CONTINUE HANDLER FOR NOT FOUND set l_last_row = 1;

Beginning of loop

fetch cursor 

BEGIN

DECLARE CONTINUE HANDLER FOR NOT FOUND -- ignore NOT FOUND errors thrown by select INTO statements when no rows are returned

select sum(amount) into l_program_fee from trans where medcase_id = l_medcase_id  and trans_type_id = 1 group by medcase_id;

-- rewrote above as follows and still didn't work
  set l_program_fee =   
    ( select sum(t11.amount) as program_fee
      from trans t11
      where t11.medcase_id = l_medcase_id and t11.trans_type_id = 1
      group by t11.MEDCASE_ID
    );

... rest of the stored proc ...

How to repeat:
See description above ..

The SQL works fine if run stand-alone (outside stored proc) - see below

set @program_fee=-1;
select sum(t11.amount) into @program_fee
      from trans t11
      where t11.trans_type_id = 1
      and t11.MEDCASE_ID = 14461
      group by t11.MEDCASE_ID;
select @program_fee;

[19 Jul 2011 20:13] Prashant Doshi

I would like to close this issue.

The problem was resolved by correction the DECLARE CONTINUE HANDLER statement as follows:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END; -- ignore NOT FOUND errors thrown by select INTO statements when no rows are returned

I was missing the BEGIN END; block on the DECLARE line