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