IF/THEN statement producing syntax error in MySQL

Question

I have a very basic IF statement written that will be a part of a trigger, but when I try to run the query I receive a syntax error:

IF (STR_TO_DATE('09/29/2017','%m/%d/%Y') > CURDATE() - INTERVAL 1 DAY) THEN
    SELECT * FROM apikeys;
END IF;

This is the error I'm getting:

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 IF' at line 1

I'm using MySQL 5.6

UPDATE: I now understand that IF/THEN statements can only be stored in procedures like triggers and are unable to be executed in this manner normally. Is there any way of knowing a trigger will be successful before implementing the new code into a live system? This trigger specifically controls a text messaging queue that fires SMS through Twilio, so I really cannot afford to guess and check.

IF... THEN ....END IF is only usable in stored procedures, triggers, etc... — Uueerdo
– Uueerdo, Commented Sep 29, 2017 at 17:42

Uueerdo · Accepted Answer · 2017-09-29 18:17:17Z

2

Procedural logic in MySQL is only usable in stored procedures, triggers, events, etc... You cannot conditionally execute a query outside of those places (or client code obviously); the closest you can get is to make the IF's condition a part of the query's WHERE conditions. You still get a result set, but it will be empty.

SELECT * 
FROM apikeys
WHERE STR_TO_DATE('09/29/2017','%m/%d/%Y') > CURDATE() - INTERVAL 1 DAY
;

Edit: In situations where you absolutely positively need procedure logic; you can create a stored procedure with it, execute that, and then drop the procedure.

Edit2:

Is there any way of knowing a trigger will be successful before implementing the new code into a live system?

Seems, obvious but the best way to know is to run it on a test system. If that is not an option for some reason, as long as you are not using the NEW or OLD features of triggers you should be able to copy it into the body of a test procedure and run that instead...but you also won't get warned if you have queries that attempt to modify the table the trigger is on.

edited Sep 29, 2017 at 18:17

answered Sep 29, 2017 at 17:45

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

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

6 Comments

Jodo1992 Over a year ago

This snippet of code WILL be stored in a trigger, so if there is any way to ensure what I wrote would be correct, I would like to keep the same IF -> THEN logic. I will adjust my question accordingly

xQbert Over a year ago

Definitely faster. than the over engineered cross join I tried.

Uueerdo Over a year ago

@Jodo1992 As far as I know, you can't really SELECT in a trigger. I mean you could; but then I'd expect a single result SELECT with assignments to variables, or a CURSOR, to be used. Either way, I don't think a trigger can return a result set.

Uueerdo Over a year ago

@xQbert we all have those moments. ;)

Jodo1992 Over a year ago

@Uueerdo you absolutely can use SELECT in triggers. I do it all the time

|

Collectives™ on Stack Overflow

IF/THEN statement producing syntax error in MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related