Set variable inside cursor for to use in select statement This is my query to create procedure, but its displaying error like : 


  'DECLARE curBalRows CURSOR FOR
  
  select if(


What I'm missing here?

    DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
    IN `p_ind` INT(20), 
    IN `p_scode` VARCHAR(200)
) 


BEGIN

DECLARE finished INT DEFAULT 0;
DECLARE balanced DECIMAL(20,3) DEFAULT 0;
DECLARE orderId INT DEFAULT 0;

select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
select @balance=0;
select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

DECLARE curBalRows CURSOR FOR 



select if( allotted_unit &gt;= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit &gt;= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;

OPEN curBalRows;

getRows: LOOP
    FETCH curBalRows INTO balanced, orderId;
        IF finished = 1 THEN 
            LEAVE getRows;
        END IF;

                UPDATE orders SET balance=balanced where order_id=orderId;

END LOOP getRows;
CLOSE curBalRows;

END $$

DELIMITER ;
DECLARE statement must posess at the beginning of BEGIN-END block, before any other statements. Use nested BEGIN-END:

DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
IN `p_ind` INT(20), 
IN `p_scode` VARCHAR(200)
) 

BEGIN

    DECLARE finished INT DEFAULT 0;
    DECLARE balanced DECIMAL(20,3) DEFAULT 0;
    DECLARE orderId INT DEFAULT 0;

    select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
    select @balance=0;
    select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

    BEGIN  -- add nested BEGIN-END block

        DECLARE curBalRows CURSOR FOR 
        select if( allotted_unit &gt;= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit &gt;= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

        OPEN curBalRows;

        getRows: LOOP
            FETCH curBalRows INTO balanced, orderId;
            IF finished = 1 THEN 
            LEAVE getRows;
            END IF;

            UPDATE orders SET balance=balanced where order_id=orderId;

        END LOOP getRows;
        CLOSE curBalRows;

    END;  -- finalize nested BEGIN-END block

END $$

DELIMITER ;


PS. Remember - declared construction exists only within the block where it is defined. So whole code between cursor declaration and cursor closing must be included into the block - finalizing END cannot be placed earlier.