CODEWITHSUNDEEP
mysqlif-statement
Myy
Myy

Reputation: 18527

MySQL If statement issue

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

Upvotes: 6

Views: 20455

Answers (3)

Rana Aalamgeer
Rana Aalamgeer

Reputation: 712

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 ;

Upvotes: 3

Myy
Myy

Reputation: 18527

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

Upvotes: 3

Vlad Miller
Vlad Miller

Reputation: 2279

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

Upvotes: 9

Related Questions