0

I want to use different where conditions, which depends on input parameter in procedure.

CREATE PROCEDURE proc_IF (IN param1 INT)  
BEGIN  
    SELECT * FROM articles 
    IF param1 = 0 THEN  
        WHERE name = 'Тест'
    ELSE  
        WHERE name = 'Проверка'  
    END IF;  
END
Vladimir Golub
  • 523
  • 1
  • 8
  • 22

2 Answers2

2

You can use case when when there are more than two conditions

CREATE PROCEDURE proc_IF (IN param1 INT)  
BEGIN  
    SELECT * FROM articles 
    WHERE name = 
    (
       CASE WHEN param1 = 0 THEN 'Тест'
       ELSE 'Проверка' END
    );
END

If there are only two conditions,then you can use IF instead

CREATE PROCEDURE proc_IF (IN param1 INT)  
BEGIN  
    SELECT * FROM articles 
    WHERE name = IF(param1 = 0,'Тест','Проверка');
END
flyingfox
  • 13,414
  • 3
  • 24
  • 39
  • If there is no else? – Vladimir Golub Jul 26 '21 at 12:29
  • @VladimirGolub what do you mean by no else? If there is no else then you can just use `where` directly or check https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008 – flyingfox Jul 26 '21 at 12:32
  • `SELECT * FROM articles IF param1 = 0 THEN WHERE name = 'Тест'` , no else condition. – Vladimir Golub Jul 26 '21 at 12:39
  • @VladimirGolub I think the better way is to create a dynamic sql or use some language framework to control it,such as `mybatis`,for dynamic sql(which I think is a bad practice),you can check this https://stackoverflow.com/questions/42383446/how-to-generate-dynamic-where-condition-in-mysql-procedure – flyingfox Jul 26 '21 at 12:43
0

I would suggest avoiding CASE expressions in the WHERE clause in general. You can use:

CREATE PROCEDURE proc_IF (IN param1 INT)  
BEGIN  
    SELECT a.*
    FROM articles a
    WHERE (param1 = 0 AND name = 'Тест') OR
          (not param1 <=> 0);
END
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786