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

Question

I am running below stored procedure in PhpMyAdmin:

DELIMITER //

CREATE PROCEDURE usp_ForgotPassword (
    -- Add the parameters for the stored procedure here
    p_mobileNo VARCHAR(50),
    p_emailId VARCHAR(300),
    p_password VARCHAR(300),
    p_otp VARCHAR(50),
    p_appInstanceCode CHAR(36))
BEGIN   
    DECLARE v_IsOTPValid TINYINT DEFAULT  NULL;
    SET v_IsOTPValid = NULL;
    DECLARE v_userCode CHAR(36);

    IF (v_IsOTPValid = 1)
    THEN
        IF EXISTS(SELECT * FROM OTP WHERE otp = p_otp AND mobileNo = p_mobileNo)
        THEN

            SET v_userCode = (SELECT userCode FROM UserLogin WHERE userName = p_emailId);

            UPDATE UserLogin UL
            SET password = p_password 
            WHERE userName = p_emailId AND userCode = v_userCode;

            -- Delete the verified OTP 
            DELETE FROM OTP WHERE otp = p_otp AND mobileNo = p_mobileNo;

            DECLARE v_Token CHAR(36);


            INSERT INTO Token (createdAt, updatedAt,code,userCode,appInstanceCode,deviceIMEI,deviceName)
                                VALUES (NOW(),NOW(),v_Token,v_userCode,p_appInstanceCode,NULL,NULL);


            SELECT 200 code, 'Password changed succesfully' as message,v_Token;
        ELSE
            SELECT 400 as code, 'Invalid OTP or Mobile No' as message,v_Token;
        END IF;
    ELSE
        SELECT 401 as code, 'OTP expired' as message,v_Token;
    END IF; 
END
//
delimiter ;

But it keep showing me an error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE v_IsOTPValid TINYINT DEFAULT NULL; SET v_IsOTPValid = NULL; DECL' at line 9

I tried:

Is declare in mysql giving syntax error?

and

Mystery error in CREATE PROCEDURE in MariaDB/MySQL

But none of those answers help!

score 4 · Accepted Answer · edited Oct 28 '19 at 11:26

4

You have a SET before the end of all DECLARE statements..

and as per official documentation, place all the declare before the set or any other statement:

BEGIN   
  DECLARE v_IsOTPValid TINYINT DEFAULT  NULL;
  DECLARE v_userCode CHAR(36);
  DECLARE v_Token CHAR(36); 
  SET v_IsOTPValid = NULL;

edited Oct 28 '19 at 11:26

Prashant Pimpale

10,349
9
44
84

answered Oct 26 '19 at 15:01

ScaisEdge

131,976
10
91
107

Got another error: `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE v_userCode CHAR(36);` – Prashant Pimpale Oct 26 '19 at 15:03
1

try move all the declare before the set – ScaisEdge Oct 26 '19 at 15:05
just s uggestion looking to your code .. Setting the v_IsOTPValid to null and fter check if is =1 seems non senso to me.. – ScaisEdge Oct 26 '19 at 15:06
Yes there is a scalar function which will set the value for it but for now (for ques) I removed it – Prashant Pimpale Oct 26 '19 at 15:07
BTW thanks! I unable to find out any documentation about it even on the other similar questions no one pointed out this! – Prashant Pimpale Oct 26 '19 at 15:09
Can you add that line to the answer? About declaring variables? will help for future readers! – Prashant Pimpale Oct 26 '19 at 15:10
this type of sintax error are easy to discover because the possible causes are restrictec to data type sintax or code position sintax .. – ScaisEdge Oct 26 '19 at 15:10
FWIW, you don't need to declare a display width for integer types, like `TINYINT(1)`. It has no effect on their size or the range of values they accept. See my answer to [Types in MySQL: BigInt(20) vs Int(20)](https://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20/3135854#3135854) – Bill Karwin Oct 26 '19 at 15:18
@BillKarwin then also the first error is related in this case to wrong SET position ?? ..ansawef updated with your suggestion .. thanks – ScaisEdge Oct 26 '19 at 15:21
All the `DECLARE` statements need to be at the beginning of the `BEGIN` block, including `DECLARE v_Token CHAR(36)` which is currently on line 27. – Bill Karwin Oct 26 '19 at 15:36

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

1 Answers1