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: dev.mysql.com/doc/refman/5.7/en/create-procedure.html — Andrew
– Andrew, Commented Jun 26, 2016 at 4:12

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 26, 2016 at 4:11

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related