How to use if else condition in MariaDB stored procedure?

Question

When I am trying to use IF Else condition in my stored procedure, I am getting the syntax error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sortby = 1 THEN value2, value3 ASC;

As per documentation, the syntax is

IF condition THEN
    statements;
ELSE
    else-statements;
END IF;

My Stored Procedure:

DELIMITER $$
      CREATE PROCEDURE test(IN _code  INT, IN testformat SMALLINT(5), IN testcurrency CHAR, IN testdate Date, IN tesval CHAR(1),  IN sortby INT)
      BEGIN
           SELECT   value1, valu2, value3, value4
           FROM     shop    
           WHERE     CODE1  = _code 
           ORDER BY  
          IF  sortby = 1  THEN
             value2, value3 ASC;
          ELSE 
            value2, value3 DESC;
         END;
     END$$
DELIMITER ;

I couldn't find the issue. Thanks for your help.

Bill Karwin · Answer 1 · 2021-07-26T14:18:27.380

The IF/THEN/ELSE structure is a statement on its own. It is not an expression you can put in the ORDER BY clause of a query.

But you can't use the IF(condition, expr1, expr2) function, because that function must return a scalar, not a list of columns and an SQL keyword.

The solution to your question is:

DELIMITER $$
  CREATE PROCEDURE test(
    IN _code  INT, 
    IN testformat SMALLINT(5), 
    IN testcurrency CHAR, 
    IN testdate Date, 
    IN tesval CHAR(1),
    IN sortby INT)
  BEGIN
    IF sortby = 1 THEN
       SELECT   value1, valu2, value3, value4
       FROM     shop    
       WHERE     CODE1  = _code 
       ORDER BY value2, value3 ASC;
    ELSE
       SELECT   value1, valu2, value3, value4
       FROM     shop    
       WHERE     CODE1  = _code 
       ORDER BY value2, value3 DESC;
    END IF;
  END$$
DELIMITER ;

Since the only difference is an SQL keyword (not a value), the only way to shorten this would be to use dynamic SQL:

DELIMITER $$
  CREATE PROCEDURE test(
    IN _code  INT, 
    IN testformat SMALLINT(5), 
    IN testcurrency CHAR, 
    IN testdate Date, 
    IN tesval CHAR(1),
    IN sortby INT)
  BEGIN
    SET @query = CONCAT('
       SELECT   value1, valu2, value3, value4
       FROM     shop    
       WHERE     CODE1  = _code 
       ORDER BY value2, value3 ', IF(sortby=1, 'ASC', 'DESC'));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END$$
DELIMITER ;

Thank you for the better shortend solution. I learned something new — Ullan, Feb 17 '21 at 18:42
It should be 'END IF' for closing the IF condition and not just 'END' — Yair.R, Jul 26 '21 at 10:34
@Yair.R, Thank you for spotting that, I have edited the answer to fix it. — Bill Karwin, Jul 26 '21 at 14:19

How to use if else condition in MariaDB stored procedure?

1 Answers1