Page 1 of 1

Stored routine does not work

Posted: Wed Apr 15, 2009 6:23 am
by raydona
Hi,
Could someone please let me know what is wrong with the following routine.

CREATE PROCEDURE ToVerifyEmployee(IN surname VARCHAR(15),IN email VARCHAR(40),OUT query INT)
BEGIN
DECLARE strA VARCHAR(15);
DECLARE strB VARCHAR(40);
SET strA = '';
SET strB = '';
SELECT LastName, EmailAddress
FROM Employee
INTO strA, strB
IF(strA = surname AND strB = email)
THEN SET query = 1;
ELSE SET query = 0;
END IF;
END;

I get the following error:
#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 '' at line 3. I cannot find the error. Also I did not change the delimiter to something else than ; before declaring the procedure. I'll be grateful for all help.

Re: Stored routine does not work

Posted: Wed Apr 15, 2009 10:30 am
by andyhoneycutt
Try changing the delimiter to something other than a semicolon. Excerpt from http://www.mysql.com/news-and-events/ne ... 00297.html
We had to invent the DELIMITER statement so that we could use semicolons inside the CREATE PROCEDURE. The MySQL client ordinarily thinks that ";" means end of statement. By saying "we'll use something else for end-of-statement marker" we make multi-statement procedures possible. Incidentally, the MySQL Reference Manual suggests | as a delimiter, but I prefer // because I sometimes need to use || as an operator within an SQL expression.
-Andy