Mysql Stored procedure with cursor

database-cursor, mysql, stored-procedures

Solution

There is no `THEN` nor `END IF` keywords, the procedure cannot compile. Check this link for proper syntax of `IF` statement: http://dev.mysql.com/doc/refman/5.7/en/if.html Use `EXIST` operator instead of `(SELECT count(*)... ) <=0`, read this link to know the reason: http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

IF    EXISTS(  
               SELECT null FROM table1 
               WHERE IndentityNo=IdentityNo1 
                 AND clientNo=@clientNo
      )
THEN
              INSERT INTO table1 (Name,IdentityNO) VALUES (name,IdentityNO1);
              INSERT INTO table2 (Address,City) VALUES(address,city);
ELSE
              INSERT INTO tblexceptional(Name,Address,City,IdentityNo)
              VALUES(name,address,city,IdentityNo1);
END IF;

I recommend using some prefixes for procedure arguments and variable names to avoid ambiguity, for example use `p_` for parameters and `v_` for variables. It's hard to guess, looking at this code, which name is a column name, a variable or a procedure parameter. This can lead to mistakes and errors. Avoid using `SELECT *` - this code will fail if someone will change the table structure. Explicitely list required columns in the cursor declaration:

declare cur1 cursor for 
        select name,Address,City,IdentityNO,clientNo
        from temptable;

The corrected procedure might look like this:

CREATE PROCEDURE `insertData`(In p_clientNo INT,In p_usedID INT)
BEGIN

declare v_name varchar(100);
declare v_address varchar(100);
declare v_city varchar(50);
declare v_IdentityNO varchar(20)
declare v_clientNo int


declare cur1 cursor for 
        select name,Address,City,IdentityNO,clientNo
        from temptable;
declare continue handler for not found set done=1;

    set done = 0;
    open cur1;
    igmLoop: loop
        fetch cur1 into v_name,v_Address,v_City,v_IdentityNO,v_clientNo;
        if done = 1 then leave igmLoop; end if;

        //If no record exists in some records table1,table2.
        IF  EXISTS(  SELECT 1 FROM table1 
                     WHERE IndentityNo = v_IdentityNo 
                       AND clientNo = v_clientNo)
              INSERT INTO table1 (Name,IdentityNO) VALUES (v_name,v_IdentityNO);
              INSERT INTO table2 (Address,City) VALUES(v_address,v_city);
        ELSE
              INSERT INTO tblexceptional(Name,Address,City,IdentityNo)
              VALUES(v_name,v_address,v_city,v_IdentityNo);
        END IF;
    end loop igmLoop;
    close cur1;
END

Problem

Original source