using IF in MySQL (not the function)

Question

I have what feels like a simple question, but can't seem to get it right. I'm just trying to execute a regular IF ... THEN ... logic, but can't seem to get it right:

set @var:=2;
if @var=1 THEN select 'hello';

I get:

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 @var=1 THEN select 'hello'' at line 1

What am I missing?

You can execute these statements only in a procedure or function. — juergen d
– juergen d, Commented Nov 12, 2012 at 10:29
it's almost hard to believe. do they have any justification for such a cruel and unusual requirement? — Mordechai
– Mordechai, Commented Nov 12, 2012 at 14:47

nawfal · Accepted Answer · 2012-11-12 15:20:10Z

2

You can use CASE instead.

SET @var:=2; 
SELECT CASE WHEN @var=1 THEN 'hello' ELSE 'no hello' END;
--prints 'no hello'

SET @var:=2; 
SELECT CASE WHEN @var:=1 THEN 'hello' ELSE 'no hello' END;
--prints 'hello'

I hope the idea is clear with above examples.

Edit: to address OP's additional concerns, You can incorporate selects in case statements, but you should enclose in brackets. For eg.

SET @var:=2; 
SELECT CASE WHEN @var:=1 THEN (select 'hello') ELSE (select 'no hello') END;

One thing to notice is that it should return back only one value (from one row and a column)

edited Nov 12, 2012 at 15:20

answered Nov 12, 2012 at 10:29

nawfal

73.8k59 gold badges342 silver badges379 bronze badges

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

9 Comments

Mordechai Over a year ago

thanks, but it won't work for something even slightly more complicated such as for example: if @var=1 select *from tableX else select * frm tableY

nawfal Over a year ago

@MorDeror like what? I'm sure there is a way out

nawfal Over a year ago

@MorDeror it works too. see my edit. If you are telling us what exactly are u trying to achieve, then we can help better

Mordechai Over a year ago

thanks @nawfal, but getting just one column back is probably not going to cover most use cases

nawfal Over a year ago

@MorDeror Yes it wont cover most cases, but you having to get retrieve multiple columns through if else logic is a code smell. I smell a design flaw. I ask you to post the actual requirement, there will be always a better alternative

|

eisberg · Accepted Answer · 2012-11-12 15:11:38Z

0

You can, but only inside of functions, procedures and triggers like so:

DELIMITER //
DROP PROCEDURE IF EXISTS anyname//
CREATE PROCEDURE anyname()
BEGIN
    IF @var1 = 1 THEN
        SELECT 'hello';
    END IF;
END//
SET @var1 := 1;
CALL anyname()//

answered Nov 12, 2012 at 15:11

eisberg

3,7812 gold badges29 silver badges38 bronze badges

5 Comments

eisberg Over a year ago

I think all of us need more details on your goal before we can offer a qualified answer.

Mordechai Over a year ago

what details? I'm starting to think that I'm the only one that has ever used a conditional statement in a batch mode :) If you need any additional information please ask away.

Mordechai Over a year ago

here is one example of a work around that I found. Although would like to avoid it if I could: stackoverflow.com/questions/9899663/…

eisberg Over a year ago

So you are unable to put your script inside of a procedure?

Mordechai Over a year ago

not in this case. I'll just go with the wierd workarounds. thanks.

Collectives™ on Stack Overflow

using IF in MySQL (not the function)

2 Answers 2

9 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related