YouHoGeon YouHoGeon - 4 months ago 14
MySQL Question

It occurs #1054 Error when I call procedure in mysql

CREATE PROCEDURE test_proc (in name varchar(100),out return_msg varchar(3000))
BEGIN
BEGIN
DECLARE v_return_msg VARCHAR(3000); -- return message
DECLARE v_error_flag INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_error_flag = -1;

START TRANSACTION;

INSERT INTO `tablename` (name, value) VALUES (name, 'Test2');
END;

IF v_error_flag < 0 THEN
ROLLBACK;
CALL DBMS_OUTPUT.PUT_LINE('Error : ' || mysql_error );
SELECT * FROM INSERT_INFO; -- UPDATE_TABLE_USED
SHOW ERRORS;
ELSE
COMMIT;
CALL DBMS_OUTPUT.PUT_LINE('Sucess : ' || sqlerrm );
SELECT 'Process succeed!!!';
END IF;
END


This is my mysql query(procedure).

However, When I CALL my procedure, It occurs error.


MySQL Message: #1054 - Unknown column 'v_error_flag' in 'field list'


I think IF statement occur error.

IF v_error_flag < 0 THEN


However, I don't know how can I fix it.

Answer

The v_error_flag is declared with in the BEGIN ... END.

The IF v_error_flag < 0 THEN condition is out of the block so v_error_flag is not recognized and throwing the error.

You need to move the IF v_error_flag < 0 THEN condition inside the BEGIN ... END block.

or

Simply remove or comment the Second BEGIN ... END block as:

CREATE PROCEDURE test_proc (in name varchar(100),out return_msg varchar(3000))                                             
BEGIN
    -- BEGIN  <-- remove/comment this
    DECLARE v_return_msg VARCHAR(3000);   -- return message
    DECLARE v_error_flag INT DEFAULT 0; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION  SET v_error_flag = -1; 

    START TRANSACTION;

    INSERT INTO `tablename` (name, value) VALUES (name, 'Test2');
    -- END;  <-- remove/comment this

    IF v_error_flag < 0 THEN 
            ROLLBACK; 
            CALL DBMS_OUTPUT.PUT_LINE('Error : ' || mysql_error );
            SELECT * FROM INSERT_INFO; -- UPDATE_TABLE_USED
            SHOW ERRORS;
    ELSE 
            COMMIT; 
            CALL DBMS_OUTPUT.PUT_LINE('Sucess : ' || sqlerrm );
            SELECT 'Process succeed!!!';
    END IF;
END
Comments