Stored routine does not work

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
raydona
Forum Newbie
Posts: 21
Joined: Sat Mar 21, 2009 9:17 am

Stored routine does not work

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Stored routine does not work

Post 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
Post Reply