Mysql – Is a query inside thesql IF clause allowed

MySQL

DELIMITER $$

CREATE PROCEDURE `InsertUser`(IN passwd VARCHAR(100), IN userType VARCHAR(20), IN mobileNo VARCHAR(20), IN refId BIGINT(20), IN companyName VARCHAR(100), IN companyMail VARCHAR(200), IN status CHAR(1)  )
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE c VARCHAR(20);
Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

IF c > 0 
THEN

    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Mobile Number Exists';
ELSE



    INSERT INTO `user`
        (   
        `password` , 
    `user_type` ,
        `mobile_no` ,
        `reference_id` ,
        `company_name` ,
        `company_mail_id` ,
        `status`
        )
  
    VALUES 
        (
        pass , 
        usertype ,
        mobileno ,
        refid ,
        companyName ,
        companyMail ,
        status
        );
END;
END$$

DELIMITER ;

Can anyone point out the error? It might be something very small, but I've tried a lot, and I'm just not able to make this run.

Documentation

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 'where mobile_no = mobileNo;

IF c > 0
THEN

    SIGNAL SQLSTATE '' at line 7

Best Answer

You have a syntax error

This is not the right SQL for getting the count

Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

You do not put into c after the from clause. It goes before it.

Therefore, this is correct SQL syntax

Select COUNT(*) into c from `user` where `mobile_no` = mobileNo;

Give it a Try !!!

Related Question