MYSQL Stored Procedures If statement Problem

Question

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?

David M · Accepted Answer · 2009-07-17 08:23:14Z

20

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

answered Jul 17, 2009 at 8:23

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

systemsfault Over a year ago

holy cow , how could I miss that :)

David M Over a year ago

It's nigh on impossible to spot typos in your own code, don't worry. Let's hear it for pair programming or, if you can't do that, SO... :D

Monolo · Accepted Answer · 2011-12-23 10:58:14Z

5

Just need space in end if in the stored procedure

edited Dec 23, 2011 at 10:58

Monolo

18.2k17 gold badges71 silver badges104 bronze badges

answered Dec 23, 2011 at 10:33

mona

511 silver badge2 bronze badges

2 Comments

Tim Over a year ago

Can you justify your answer? E.g. with a link to the documentation?

Tivie Over a year ago

Justification? mona just copied the answer above

user2606556 · Accepted Answer · 2013-07-22 11:01:09Z

0

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

answered Jul 22, 2013 at 11:01

user2606556

1

Collectives™ on Stack Overflow

MYSQL Stored Procedures If statement Problem

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related