CODEWITHSUNDEEP
mysqlstored-procedures
Kasper Sølvstrøm
Kasper Sølvstrøm

Reputation: 280

MySQL Stored procedure "DECLARE is not a valid"

I'm trying to made a stored procdure, to add 1 to an assignments downloads and subtract 1 from a Users credit.

My best guess:

CREATE DEFINER=`Library`@`localhost` PROCEDURE `download`(
        IN p_assignmentId INT(11),
        IN p_userId INT(11)
    )
BEGIN
    DECLARE p_credit INT unsigned DEFAULT 1;
    DECLARE p_downloads INT unsigned DEFAULT 1;

SELECT credit
INTO p_credit 
FROM Users
WHERE userId = p_userId;

SELECT downloads
INTO p_downloads
FROM Assignments
WHERE assignmentId = p_assignmentId;


    UPDATE Users
    SET credit = p_credit - 1 
    WHERE userId = p_userId;

    UPDATE Assignments
    SET downloads = p_downloads +1
    WHERE assignmentId = p_assignmentId;


END

Its complaining about my declare statements needing a semicolon, which does not seem to be the case, so any guesses?

Upvotes: 2

Views: 122

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

MySQL Stored Procedures and Functions often need DELIMITER overrides to prevent their own body statements from prematurely terminating their CREATE statement; this is conventionally accomplished with this kind of template:

DELIMITER $$

CREATE ...
BEGIN 
   [stmt];
   [stmt2];
   ....
END$$

DELIMITER ;

Upvotes: 2

Related Questions