T-SQL use different WHERE clause based on value of input parameter

Question

I would like to change the WHERE clause of a query based upon the value of an input parameter. I'm guessing this is possible, but it seems that I'm approaching it in the wrong way?

A simplified version on my SP query:

CREATE PROCEDURE [dbo].[GetMailboxMessagesByStatus]
    @UserId             UNIQUEIDENTIFIER,
    @MessageStatus      INT
AS
BEGIN
    SELECT  * 
    FROM  MailboxMessages m
    WHERE
    CASE @MessageStatus
        WHEN 4 THEN m.SenderId = @UserId    --Sent
        ELSE m.RecipientId = @UserId        --Inbox
    END
END 
GO

Using a `CASE` expression in an `ON` or `WHERE` clause is certainly possible, e.g. [this](https://stackoverflow.com/a/10260297/92546) example. In many cases it isn't the best solution. — HABO, Jul 02 '17 at 23:46

MWillemse · Answer 1 · 2017-07-03T09:55:06.170

15

No need for case or iif constructs:

WHERE @MessageStatus = 4  AND  m.SenderId = @UserId
   OR @MessageStatus <> 4 AND  m.RecipientId = @UserId

EDIT: Be aware on big tables using this construct when the table being queried is quite large. Using 2 seperate queries using a IF statement like Chester Lim suggested might be the better solution. Preventing parameter sniffing might also be a good idea

edited Jul 03 '17 at 09:55

answered Jul 02 '17 at 21:33

MWillemse

960
5
9

1

This is the best answer unless you are going to use dynamic SQL. – Gordon Linoff Jul 02 '17 at 22:24
6

how about using parentheses in this answer? – ViKiNG Jul 03 '17 at 04:46
1

@ImRaN You could if you want to make it clearer to you in what order the expression is evaluated but it is not necessary as the rules for evaluation state it should first evaluate the AND expression than the OR expression. – MWillemse Jul 03 '17 at 09:40
1

Thanks MWillemse. But as per my understandings, in above expression, if @MessageStatus <> 4 check is true, the compiler will not care about any other condition at all. – ViKiNG Jul 03 '17 at 21:04
1

That ain't true, try following statement `SELECT 1 WHERE 1=0 AND 1=1 OR 1<>0 AND 1=0`, if you are right it should return a record but it doesn't. – MWillemse Jul 04 '17 at 08:32
To further clarify my last statement, the proposed statement would be evaluated by SQL (and about any other language for that matter) in following three steps, 1 equal and unequal expressions, result: `SELECT 1 WHERE false AND true OR true AND false`, step 2: AND expressions, result: `SELECT 1 WHERE false OR false` and lastly the OR expressions: `SELECT 1 WHERE false` – MWillemse Jul 04 '17 at 08:49
Can you please explain why this method is inefficient for larger tables? – jayasurya_j Mar 26 '20 at 14:38

Chester Lim · Answer 2 · 2017-07-02T21:24:36.400

3

Use an IF statement.

CREATE PROCEDURE [dbo].[GetMailboxMessagesByStatus]
    @UserId UNIQUEIDENTIFIER ,
    @MessageStatus INT
AS
    BEGIN

        IF ( @MessageStatus = 4 )
            BEGIN
                SELECT  *
                FROM    MailboxMessages
                WHERE   m.SenderId = @UserId;    --Sent
            END;

        ELSE
            BEGIN
                SELECT  *
                FROM    MailboxMessages m
                WHERE   m.RecipientId = @UserId;        --Inbox
            END; 

    END;

GO

EDIT - a much better way provided by LukStorms (since i did not know IIF until i saw his answer)

CREATE PROCEDURE [dbo].[GetMailboxMessagesByStatus]
    @UserId UNIQUEIDENTIFIER ,
    @MessageStatus INT
AS
    BEGIN
        SELECT  *
        FROM    MailboxMessages m
        WHERE IIF (@MessageStatus = 4, m.SenderId, m.RecipientId) = @UserId;    --Sent
    END       
GO

edited Jul 02 '17 at 21:24

answered Jul 02 '17 at 21:18

Chester Lim

459
7
19

2

I think your first method will be more efficient though. More code, but faster. – LukStorms Jul 02 '17 at 21:29
First code is better than above answer provided search condition do not change later . – KumarHarsh Jul 03 '17 at 07:56
I see. Thank you guys for letting me know! – Chester Lim Jul 03 '17 at 09:56

LukStorms · Answer 3 · 2017-07-02T21:28:49.323

You could change that WHERE clause to

WHERE (CASE WHEN @MessageStatus = 4 THEN m.SenderId ELSE m.RecipientId END) = @UserId

Because what you put after the THEN in a CASE should just be a value, not a comparison.

Or use IIF instead of a CASE:

WHERE IIF(@MessageStatus = 4,m.SenderId,m.RecipientId) = @UserId

But the SQL will run more efficient if you use an IF ... ELSE ... and run a different Query based on the @MessageStatus.

Was writing an example for that, but Chester Lim already beat me to it. ;)
(so no need to repeat that approach in this answer)

T-SQL use different WHERE clause based on value of input parameter

3 Answers3

Linked