1

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?

Community
  • 1
  • 1
Mordechai
  • 718
  • 1
  • 8
  • 23

2 Answers2

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)

nawfal
  • 70,104
  • 56
  • 326
  • 368
  • 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 – Mordechai Nov 12 '12 at 14:49
  • @MorDeror like what? I'm sure there is a way out – nawfal Nov 12 '12 at 14:51
  • @MorDeror it works too. see my edit. If you are telling us what exactly are u trying to achieve, then we can help better – nawfal Nov 12 '12 at 15:05
  • thanks @nawfal, but getting just one column back is probably not going to cover most use cases – Mordechai Nov 12 '12 at 15:24
  • @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 – nawfal Nov 12 '12 at 15:30
  • it seems to me that retrieving data from a certain table given a condition is not such a terrible design flaw. :) As far as what I was originally after was to check if a row exists before attempting an insert. – Mordechai Nov 12 '12 at 15:49
  • @MorDeror it is not, and that is why you have `WHERE` and `CASE` and `IF` but they are meant for different purposes. You are kind of asking for `WHERE`'s functionality in `CASE`. If you want to conditionally insert then thats a complete different thing but which is easily do-able than these hacks. See this link for an example http://stackoverflow.com/questions/13302714/mysql-conditional-insert-with-dynamic-values/13303723#13303723 There are plenty other similar links too in SO – nawfal Nov 12 '12 at 16:08
  • thanks. It solves my problem, though enforces my opinion that MySQL should not be used for any real DB work. :) – Mordechai Nov 12 '12 at 16:26
  • You're welcome. I do agree MySQL is not really the best out there. Its not at all feature rich compared to other Dbms-es - it misses intersect, except/minus, pivoting capability, window functions and is farther from std ANSI SQL compared to other major players, not forgetting the illogical things it lets us do with its group by. But the reason its so popular is that its plain easy, something for which I like. Not just syntactically but even by not constraining us with roadblocks. – nawfal Nov 12 '12 at 16:36
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()//
eisberg
  • 3,731
  • 2
  • 27
  • 38
  • I think all of us need more details on your goal before we can offer a qualified answer. – eisberg Nov 12 '12 at 15:26
  • 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 Nov 12 '12 at 15:56
  • here is one example of a work around that I found. Although would like to avoid it if I could: http://stackoverflow.com/questions/9899663/if-condition-in-mysql-script – Mordechai Nov 12 '12 at 16:09
  • So you are unable to put your script inside of a procedure? – eisberg Nov 13 '12 at 07:24
  • not in this case. I'll just go with the wierd workarounds. thanks. – Mordechai Nov 13 '12 at 07:33