MySQL - If query returns error

Question

I want an if query like below.

set @i=1;
set @j=2;
if @i < @j then select * from test limit 1;
end if;

Im getting the below 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 'if @i < @j then select * from test limit 1' at line 1

Also tried this in procedure,

DELIMITER $$
DROP PROCEDURE IF EXISTS test $$
create procedure test ()
set @i=1;
set @j=2;
if @i < @j then select * from test limit 1;
end if;
END $$


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 'if @i < @j then select * from test limit 1;
end if;
END' at line 1

Can anyone help me to fix this?

Unless this code is in a stored procedure or trigger, MySQL has no support for IF..THEN statements. — Bill Karwin
– Bill Karwin, Commented Oct 27, 2017 at 22:40

Gordon Linoff · Accepted Answer · 2017-10-28 01:03:13Z

1

You need a begin:

DELIMITER $$
DROP PROCEDURE IF EXISTS test $$
create procedure test ()
begin
    set @i=1;
    set @j=2;
    if @i < @j then
         select * from test limit 1;
    end if;
END $$

DELIMITER ;

Your first doesn't work because if is only allowed in programming blocks. In fact, that is why your version doesn't work either -- without the begin the if is not in a programming block.

edited Oct 28, 2017 at 1:03

answered Oct 27, 2017 at 22:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

TheDataGuy Over a year ago

thanks, but it didn't return any values. It'll go infinite while hit enter. call test(); -> -> -> ->

Barmar Over a year ago

Type DELIMITER ; afterwards to go back to the normal delimiter.

Barmar · Accepted Answer · 2017-10-27 22:57:03Z

1

Instead of using IF, put the test in the query:

SELECT * FROM TEST
WHERE @i < @j
LIMIT 1

answered Oct 27, 2017 at 22:57

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

MySQL - If query returns error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related