Pasted

DROP PROCEDURE IF EXISTS prc_usergold_updategoldbyid;
 
CREATE PROCEDURE prc_usercharacter_updategoldbyid(v_id INT, v_gold BIGINT, OUT v_totalCoin BIGINT, OUT v_result TINYINT)
BEGIN
    DECLARE v_currentGold BIGINT;
    DECLARE v_rs INT;
    SELECT gold INTO v_currentGold FROM usergold WHERE userid = v_id;
    IF v_currentGold + v_rs < 0 THEN
        SET v_result = 0;
        SET v_totalCoin = v_currentGold;
    ELSE
        START TRANSACTION;
            UPDATE usergold SET gold = gold + v_gold WHERE userid = v_id;
            SELECT ROW_COUNT() INTO v_rs;
        IF(v_rs > 0) THEN
            COMMIT;
            SELECT gold INTO v_totalCoin FROM usergold WHERE userid = v_id;
            SET v_result = 1;
        ELSE
            ROLLBACK;
        END IF;
    END IF;
    
END
--test
CALL prc_usergold_updategoldbyid(10, -999, @total, @result);
SELECT @total, @result