Question 264427 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – IF-THEN statement with user-defined variable

In MySQL I would like to compare a user-defined variable via an IF-THEN statement:

SET @num = 10;

IF @num > 7 then
    select * from person;
ELSE
    select name from person;
END IF;

How could I do that?
Currently I get an error near the IF @num > 7.

Tags:

3

Answers


  1. 0

    You cannot use If-then statement directly in a SELECT query.

    But you can use a conditional operator like this :

    SET @num = 10;

SELECT 
    CASE WHEN @num > 7 THEN * ELSE name END
FROM person
    Login or Signup to reply.
  2. 0

    If you want to add logical operators like IF-THEN statements in your queries, use stored procedures:

    DELIMITER $$
create procedue sp_persons( in_num int )
begin

IF in_num > 7 then
    select * from person;
ELSE
    select name from person;
END IF;

end
$$

call sp_persons( 10 );
    Login or Signup to reply.
  3. 0

    Compound statements like IF/THEN/ELSE are only supported in MySQL’s stored routine language.

    This is from the manual:

    This section describes the syntax for the BEGIN … END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events.

    Typically if you need to run SQL queries conditionally and you aren’t using stored routines, you would put the conditional logic into some client application programming language, e.g. Java, Python, Go, PHP, or whatever is your favorite language.

    From its earliest days, SQL was intended to be used in combination with other programming languages.

    If you really can’t write the code in a client application, here’s a workaround that can work for instance to run your example in an SQL script:

    SET @num = 10;

SET @sql = IF(@num > 7,
              'select * from person',
              'select name from person');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    If you have more complex conditional code, this workaround might not be practical.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search