1
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 
  • What error message is returned? – Michael Green Nov 23 '14 at 7:53
  • #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 '; DECLARE c VARCHAR(20); Select COUNT(*) from user into c where mobile_no = mo' at line 4 – Mukul Shukla Nov 23 '14 at 8:11
  • I've tried writing this in various ways! First I was directly comparing the result of count inside if, that did not work, so I wrote it as a separate query – Mukul Shukla Nov 23 '14 at 8:12
  • Im sorry Matt, I somehow forgot to write that here.. the whole code is inside the handler.. error remains the same.. its near c >0 , is such a comparison allowed? – Mukul Shukla Nov 23 '14 at 8:46
  • This is the actual code, copy/pasted this time - shrib.com/sqlproblem – Mukul Shukla Nov 23 '14 at 8:52
1

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 !!!

  • Thank you! never thought it would be so minute. A voteup requires reputation and I'm new here, but definitely a voteup from my side. – Mukul Shukla Nov 24 '14 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.