MySQL If statement issue

Question

I am trying to make this IF statement work, but I can't seem to make it do what I want. If I do a select @result, It'll give me the value 0, then why doesn't the IF statement work?

SET @message = '((sometihng here))';
select LEFT(@message, 1) into @firstChar;
select STRCMP(@firstChar,'(') into @result;
IF (@result = 0) THEN  
SET @message = 'true';
//more selects and cals here;
END IF;
select @message;

I should get true, but I don't it shows me an error:

SQL query: IF( @result =0 ) THEN SET @message = 'true';

MySQL said:

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 (@result = 0) THEN SET @message = 'true'' at line 1

score 9 · Accepted Answer · answered Mar 28 '12 at 22:55

9

try use function http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

SELECT IF(@result = 0, 'true', '((something here))') AS message

answered Mar 28 '12 at 22:55

Vlad Miller

2,255
1
20
38

score 3 · Answer 2 · answered Sep 21 '16 at 11:12

The IF .. THEN .. ELSE syntax in MySQL is only available for procedural code (stored precudures, functions, triggers..), but not for SELECT statements.

IF ELSE USED IN STORED PROCEDURE EXAMPLE BELOW

DELIMITER //

CREATE PROCEDURE NAME(IN Number INT)
BEGIN
    IF roll= 1 
          THEN  SELECT * FROM table1 WHERE id = roll;
    ELSE   
          SELECT * FROM table2 WHERE id = 2;
    END IF;
END //

DELIMITER ;

score 3 · Answer 3 · answered Mar 29 '12 at 03:43

As Max Mara pointed out, that's a good work aroud. The reason the IF wasn't working is not because the syntax is incorrect, but because flow control functions like IF ... THEN are only valid inside of stored procedures or functions, All this thanks to @TehShrike

MySQL If statement issue

3 Answers3

Linked