CODEWITHSUNDEEP
mysqlstored-procedures
systemsfault
systemsfault

Reputation: 15527

MYSQL Stored Procedures If statement Problem

I'm working with Mysql 5.1.28-rc on freebsd. I've just decided to use stored procedures in MYSQL and created a test procedure as below:

DELIMITER $$ 
DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE test( IN test VARCHAR(22) )
BEGIN
 DECLARE count INT(11);
 SET count = (SELECT COUNT(*) FROM Test WHERE test_column = test );
 SELECT count;
 IF count = 0 THEN
  SET count = 1;
 ELSE
  SET count = 2;
 ENDIF;
END $$
DELIMITER;

This procedure works well without IF statement in it , but with the if statement it gives, ERROR 1064 (42000): 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'

How can I solve this issue? Where is the problem?

Upvotes: 8

Views: 23605

Answers (3)

user2606556
user2606556

Reputation: 1

Should not use variables like count or something. So please find the solution for this-

DELIMITER $$ 
DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE test(IN test VARCHAR(22) )
BEGIN
 DECLARE var_count INT;
 SET var_count = (SELECT COUNT(*) FROM test WHERE test.textfield = test);
 IF var_count = 0 THEN
  SET var_count = 1;
 ELSE SET var_count = 2;
 END IF;
 SELECT var_count;
END $$

Upvotes: 0

mona
mona

Reputation: 51

Just need space in end if in the stored procedure

Upvotes: 5

David M
David M

Reputation: 72870

ENDIF requires a space in MySQL doesn't it? i.e. END IF

Upvotes: 20

Related Questions