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

Answer 1

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

Answer 2

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

Answer 3

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)