MySql Stored procedure creation Error

Question

I am trying to create a stored procedure in my mysql database usin the below code.I am getting an error.Please help me to solve

My MySQl version is 5.0.51b

CREATE PROCEDURE tv_insert_user(IN firstName VARCHAR(25),
IN lastName VARCHAR(25), IN emailId VARCHAR(22),IN countryId INT,IN stateId INT)
  BEGIN
     INSERT INTO tv_user_master(FIRST_NAME,LAST_NAME,EMAIL,COUNTRY_ID,STATE_ID) VALUES(firstName,lastName,lname,emailId,countryId,stateId)
  END

ERROR:

---

Error Code : 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 'END' at line 5 (0 ms taken)

Answer 1

There is an extra variable in the values list

CREATE PROCEDURE tv_insert_user(IN firstName VARCHAR(25), IN lastName VARCHAR(25), IN emailId VARCHAR(22),IN countryId INT,IN stateId INT)
BEGIN
 INSERT INTO tv_user_master(FIRST_NAME,LAST_NAME,EMAIL,COUNTRY_ID,STATE_ID)
  VALUES(firstName,lastName,lname,emailId,countryId,stateId)
                            ^^^^^^
END

try removing and see if you still get the error and if not already change the delimiter.

Answer 2

As sfossen said, remove lname.
And also, add a semicolon to the end of the insert:

CREATE PROCEDURE tv_insert_user(IN firstName VARCHAR(25), IN lastName VARCHAR(25), IN emailId VARCHAR(22),IN countryId INT,IN stateId INT)
  BEGIN
     INSERT INTO tv_user_master(FIRST_NAME,LAST_NAME,EMAIL,COUNTRY_ID,STATE_ID) VALUES(firstName,lastName,emailId,countryId,stateId);
  END

Answer 3

The lname-thing isn't what the error message is about. You need to specify a different delimiter. The default delimiter for MySQL commands is the semicolon, but semicolons are used inside stored procedures at the the end of statements. In this example I change the delimiter to //, and use that to end the procedure. This allows me to use semicolon inside the block.

DELIMITER //

CREATE PROCEDURE tv_insert_user(IN firstName VARCHAR(25),
IN lastName VARCHAR(25), IN emailId VARCHAR(22),IN countryId INT,IN stateId INT)
BEGIN
   INSERT INTO tv_user_master(FIRST_NAME,LAST_NAME,EMAIL,COUNTRY_ID,STATE_ID)   VALUES(firstName,lastName,emailId,countryId,stateId);
END;
//

DELIMITER ;

Once you've solved the delimiter bit, you would probably get a error about the INSERT command and extra attributes (lastname twice).