DriLLFreAK100 DriLLFreAK100 - 5 months ago 18
MySQL Question

MySQL - Stored Procedures syntax issue

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

Answer

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