I have been spinning my wheels trying to figure out what I have wrong. I'm new to MySQL so it could be something simple. I am creating a stored procedure to increase a user's in-game currency. I try an DECLARE a variable for the procedure and workbench gives me an error saying "not valid in this position, expecting END". Everything that I've looked up online says to do it this way. If I move where the DECLARE is to above the SET TRANSACTION I can get it so there are no errors but the procedure doesn't change the value in currency. I believe this is because the variable isn't declared and so it doesn't have anywhere to store the starting balance ergo can't add the amount to the balance. I did see some articles that mentioned not putting in the semi-colon but I tried changing that but that generates different errors. Any help would be much appreciated.
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCurrencyBalance`(userID INT, amount INT)
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE balance INT;
SET balance = (SELECT currency
FROM users
WHERE user_id=@userID);
SET @amount = balance + @amount;
UPDATE users
SET
currency = @amount
WHERE
user_id = @userID;
SELECT currency FROM users WHERE user_id=userID;
COMMIT;
END
Do not use the same names for parameters, declared variables and column, and do not confuse users defined (at) variables with local (declared variables). Your code corrected is
delimiter $$
CREATE PROCEDURE p1(p_userID INT, p_amount INT)
BEGIN
DECLARE balance INT;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET balance = (SELECT currency FROM t WHERE user_id=P_userID);
SET p_amount = balance + p_amount;
UPDATE t
SET currency = p_amount
WHERE user_id = p_userID;
#SELECT currency FROM t WHERE user_id=p_userID;
END $$
@GMB answer is better..