2

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;
3

It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want. Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block :

CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
proc_body:
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;
            LEAVE proc_body;
        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 proc_body;
  • great, thanks. I did not know LEAVE statement – Zbynek Jun 18 '15 at 18:28
0

Plan A: Put it in a MEMORY or MyISAM table.

Plan B: Have two connections; use the second to store the value you don't want rolled back.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.