CODEWITHSUNDEEP
mysqlmysql-workbenchworkbench
d1g1t4l Yug3n
d1g1t4l Yug3n

Reputation: 3

Why is DECLARE in the wrong spot? [MySQL Workbench]

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

Upvotes: 0

Views: 161

Answers (2)

GMB
GMB

Reputation: 222442

You are mixing user variables and local variables, which makes the code messy. For example userID is an argument to the function, but then you refer to it using @userID. Also, the variable names clash with actual column names of the table.

Bottom line, I don't think you need that complicated logic. It seems like a simple update statement does what you want. So your code becomes a single-statement procedure, like so:

create procedure addcurrencybalance (p_user_id int, p_amount int)
    update users
    set currency = currency + p_amount 
    where user_id = p_user_id
;

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17615

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..

Upvotes: 0

Related Questions