CODEWITHSUNDEEP
mysql
Nikhil
Nikhil

Reputation: 27

Mysql Said: #1325 Cursor is already open

I am getting error as cursor is already open, and if i fix an error, my loop executes infinite times

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_StatementOfAccont_Closingbal_new`(IN `Ac_code` BIGINT, IN `fdate` DATE, IN `tdate` DATE, IN `opening` DOUBLE, IN `dc` VARCHAR(25), IN `drsum` DOUBLE, IN `crsum` DOUBLE)
        NO SQL
Begin

    DECLARE cur_name1 CURSOR FOR select Ac_code from ledger_master;

    BEGIN
        OPEN cur_name1;
        MAIN_LOOP: LOOP         
            FETCH cur_name1 INTO Ac_code;     
            select @opening := Op_bal from ledger_master where Ac_code=Ac_code;             
            select @dc := op_dc from ledger_master where Ac_code=Ac_code;               
            select @drsum := sum(Amount) from ledgerentry where DC='dr' and Ac_code=Ac_code and  ledgerentry.date <= @tdate;                
            select @crsum := sum(Amount) from ledgerentry where DC='cr' and Ac_code=Ac_code and  ledgerentry.date <= @tdate;
            if (@dc='cr' and @opening>0) THEN
                set @opening := 0-@opening;
            END IF;
            set @opening := ifnull( @opening,0) + ifnull(@drsum,0) - ifnull(@crsum,0);

            delete from tmp_closingbalance;

            if(@opening>0) then
                insert into tmp_closingbalance values(Ac_code,@opening,0);
            ELSEIF(@opening<0) then
                set @opening := 0-@opening;
                insert into tmp_closingbalance values(Ac_code, 0,@opening );    
            ELSEIF(@opening=0) then
                insert into tmp_closingbalance values(Ac_code,0,0);    
            end if;
        END LOOP;
        CLOSE cur_name1;
    END;

END$$
DELIMITER ;

Upvotes: 1

Views: 582

Answers (1)

nbk
nbk

Reputation: 49373

You open the cursor uin the loop do it before that.

I also changed the code to include a finished Option, so that the loop don't run into NULLs

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_StatementOfAccont_Closingbal_new`(IN `Ac_code` BIGINT, IN `fdate` DATE, IN `tdate` DATE, IN `opening` DOUBLE, IN `dc` VARCHAR(25), IN `drsum` DOUBLE, IN `crsum` DOUBLE)
        NO SQL
Begin
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE cur_name1 CURSOR FOR select Ac_code from ledger_master;

    OPEN cur_name1;
    BEGIN

        MAIN_LOOP: LOOP         
            FETCH cur_name1 INTO Ac_code; 
            IF finished = 1 THEN 
                LEAVE MAIN_LOOP;
            END IF;
            select @opening := Op_bal from ledger_master where Ac_code=Ac_code;             
            select @dc := op_dc from ledger_master where Ac_code=Ac_code;               
            select @drsum := sum(Amount) from ledgerentry where DC='dr' and Ac_code=Ac_code and  ledgerentry.date <= @tdate;                
            select @crsum := sum(Amount) from ledgerentry where DC='cr' and Ac_code=Ac_code and  ledgerentry.date <= @tdate;
            if (@dc='cr' and @opening>0) THEN
                set @opening := 0-@opening;
            END IF;
            set @opening := ifnull( @opening,0) + ifnull(@drsum,0) - ifnull(@crsum,0);

            delete from tmp_closingbalance;

            if(@opening>0) then
                insert into tmp_closingbalance values(Ac_code,@opening,0);
            ELSEIF(@opening<0) then
                set @opening := 0-@opening;
                insert into tmp_closingbalance values(Ac_code, 0,@opening );    
            ELSEIF(@opening=0) then
                insert into tmp_closingbalance values(Ac_code,0,0);    
            end if;
        END LOOP;
        CLOSE cur_name1;
    END;

END$$
DELIMITER ;

Upvotes: 1

Related Questions