MySQL - Stored Procedures syntax issue

Question

CREATE PROCEDURE `usp_GetUserValidation`

(IN `@Username` VARCHAR(255), 
 IN `@Password` VARCHAR(50), 
 OUT `@ErrorCode` INT)

    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'To validate user login'
BEGIN

    IF EXISTS
    (SELECT UserID 
        FROM mt_User
        WHERE Username = `@Username`
        AND Password = PASSWORD(`@Password`))

        BEGIN
            SET `@ErrorCode` = 0;
        END

    ELSE
        SET `@ErrorCode` = 1;


    SELECT '@ErrorCode' AS res
END

Hi, guys. I am an SQL Server user. Recently, I have just started out to learn about MySQL and its stored procedures(routines) writing. Can anyone points out what is the mistake that I have made that is causing the following error? Thank you in advance guys :)

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near 'BEGIN SET @ErrorCode = 0' at line 15

i think between Begin and End you need an actual query, not just "set whatever". Based on looking at the manual as suggested in the error: http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html — Andrew, Jun 26 '16 at 04:12

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

This might be what you want. The whole thing in a DELIMITER BLOCK, and some changes to the IF block (that had a few syntax errors). Note, it now saves on my system.

DELIMITER $$
CREATE PROCEDURE `usp_GetUserValidation`

(IN `@Username` VARCHAR(255), 
 IN `@Password` VARCHAR(50), 
 OUT `@ErrorCode` INT)

    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'To validate user login'
BEGIN

    IF EXISTS
    (SELECT UserID 
        FROM mt_User
        WHERE Username = `@Username`
        AND Password = PASSWORD(`@Password`)) THEN

            SET `@ErrorCode` = 0;

    ELSE
        SET `@ErrorCode` = 1;
    END IF;


    SELECT '@ErrorCode' AS res;
END$$
DELIMITER ;

Related: What is the deal with DELIMITER.

MySQL Manual Page on IF Syntax

MySQL - Stored Procedures syntax issue

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

1 Answers1