CODEWITHSUNDEEP
mysqlsql
SamanthaAlexandria
SamanthaAlexandria

Reputation: 225

Use of IF and SET in the same SQL statement

I need to mix IF and SET statements in an SQL stored procedure. I have already declared the variables p1, so,ct,ac, yoyo1,yoyo2, etc.). I would like to achieve the following results:

I have tried the following SQL code but it returns 0:

SET p1=IF(so=0, yoyo1 AND SET so=1, 
  IF(ct=0, yoyo2 AND SET ct=1, 
     IF(ac=0 , yoyo3 AND SET ac=1,'image01.jpg')));

Upvotes: 0

Views: 58

Answers (2)

GMB
GMB

Reputation: 222432

If your code is part of a stored procedure, you can use MySQL IF syntax as follows :

IF so=0 THEN 
    SET p1 = 'yoyo1';
    SET so = 1;
ELSEIF ct = 0 THEN
    SET p1 = 'yoyo2';
    SET ct = 1;
ELSEIF ac = 0 THEN
    SET p1 = 'yoyo3';
    SET ac = 1;
ELSE
    SET p1 = 'image01.jpg';
END IF;

As explained in the documentation, beware not to mix the IF statement (the ternary operator) with the IF() function, wich is a control flow function.

Upvotes: 1

fifonik
fifonik

Reputation: 1606

    IF so=0 THEN
        SET p1='yoyo';
        SET so=1;
    ELSE
        IF ct=0 THEN
            SET p1='yoyo2';
            SET ct=1;
        ELSE
            IF ac=0 THEN
                SET p1='yoyo3';
                SET ac=1;
            ELSE
                SET p1='image01.jpg';
            END IF;
        END IF;
    END IF;

OR use CASE statement (I'd prefer this one):

    CASE
        WHEN so=0 THEN
            BEGIN
                SET p1='yoyo';
                SET so=1;
            END;
        WHEN ct=0 THEN
            BEGIN
                SET p1='yoyo2';
                SET ct=1;
            END;
        WHEN ac=0 THEN
            BEGIN
                SET p1='yoyo3';
                SET ac=1;
            END;
        ELSE
            BEGIN
                SET p1='image01.jpg';
            END;
    END CASE;

Upvotes: 0

Related Questions