CODEWITHSUNDEEP
mysqlsql
Vladimir Golub
Vladimir Golub

Reputation: 623

How to use different where conditions depending on the input parameter in procedure?

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

Upvotes: 0

Views: 555

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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

Upvotes: 0

flyingfox
flyingfox

Reputation: 13506

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

Upvotes: 2

Related Questions