MYSQL: If/Else syntax

Question

Working on a query but I need to implement some if else logic to check for a loop but I keep getting a 1064 bad syntax error.

I've gone over the manual and some examples but still no luck. So I stripped down my query to the most basic comparison below:

SET @start_date = '2019-06-01';
SET @other_date = CURDATE();

IF @start_date < @other_date THEN 
Select 'True';
ELSE
Select 'False';
END IF;

The below is the read out I receive from status:

SET @start_date = '2019-06-01' OK Time: 0.065s

SET @other_date = CURDATE()

OK Time: 0.065s

IF @start_date < @other_date THEN Select 'True'

1064 - 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 @start_date < @other_date THEN Select 'True'' at line 1 Time: 0.067s

I understand that I'm going wrong somewhere with my syntax on the IF clause but in the docs it states:

IF [condition] THEN if-statements ELSE else-statements; END IF;

I'm not sure why this is failing, any insight would be appreciated.

forpas · Accepted Answer · 2019-10-28 21:12:33Z

1

For queries there is no IF...ELSE... statement (which you can use in stored procedures/functions).
Instead you can use the function IF():

SELECT IF(@start_date < @other_date, 'True', 'False');

or the CASE expression:

SELECT CASE WHEN @start_date < @other_date THEN 'True' ELSE 'False' END;

See the demo.

answered Oct 28, 2019 at 21:12

forpas

165k10 gold badges51 silver badges85 bronze badges

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

5 Comments

Callat Over a year ago

What about for Loops? The whole reason I started looking at this and got stuck was because this logic is the control factor for a loop for a custom report. If stored procs and functions have different if/else syntax. Which is appropriate for a loop? This is what I had. WHILE (@start_date < NOW() && SUBDATE(@start_date, INTERVAL -2 WEEK) < NOW()) DO ...

forpas Over a year ago

You don't normally use loops in queries. In stored procedures/functions of course you can. If what you want is to write a stored procedure then you can use loops and if statements as well.

Barmar Over a year ago

@nbk What does this have to do with RANK?

nbk Over a year ago

i shoud you an example with a loop where you use a if clause, i thought you meant that with your question about ifs and loops

forpas Over a year ago

@nbk this is not a loop (if this is what you implied).

nbk · Accepted Answer · 2019-10-28 21:13:38Z

1

You syntax is false

SET @start_date = '2019-06-01';
SET @other_date = CURDATE();

SELECT 
IF (@start_date < @other_date, 'True','False');

The result is true

answered Oct 28, 2019 at 21:13

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

MYSQL: If/Else syntax

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related