I wish to keep local variable from stored procedure even if rollback occurres.
In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments. When I call the procedure with user_id = 1, value_from = 10, value_to = 20, it registered numbers 10, 11, ..., 19, 20 to user 1. Every number can be registered only once and only to one user.
There are two possible conflicts: I try to register number, which is already registered to another user, or I try to register number, which is already mine.
I wish to determine, which of these options occurred and store that value.
Here is my procedure, which takes also OUT parameter, which describes the result of the operation
-- create table CREATE TABLE test ( value INT NOT NULL UNIQUE, user_id INT NOT NULL ) ENGINE = INNODB; -- fast procedure with transactions DELIMITER $$ CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) ) BEGIN SET retval = 'ERROR_UNKNOWN'; START TRANSACTION; SET @val = val_from; REPEAT -- is there already record, registered to some user? SET @uid = (SELECT user_id FROM test WHERE value = @val); IF @uid IS NOT NULL THEN SET retval = 'ERROR_ANOTHER_USER'; -- is it already registered to me? IF @uid = uid THEN SET retval = 'ERROR_ALREADY_HAS'; END IF; ROLLBACK; END IF; INSERT INTO test (value, user_id) VALUES (@val, uid); SET @val = @val + 1; UNTIL @val > val_to END REPEAT; SET retval = 'OK'; COMMIT; END$$ DELIMITER ;
The problem is, that ROLLBACK not only throws away all new records in table test, it also resets the value of @retval.
Is there any way to keep the custom error message even in case of ROLLBACK
-- test - should return OK - work fine SET @retval := 'UNKNOWN'; CALL sp_insert_values(1, 10, 20, @retval); SELECT @retval; -- test - should return 'ERROR_ANOTHER_USER' - throws error and resets @retval, should SET @retval := 'UNKNOWN'; CALL sp_insert_values(1, 1, 30, @retval); SELECT @retval; -- test - should return 'ERROR_ALREADY_HAS' - throws error and resets @retval SET @retval := 'UNKNOWN'; CALL sp_insert_values(2, 15, 30, @retval); SELECT @retval;