TSQL Case in where statement if parameter is null

Question

I have a SP that gives me a lot of hard times.

The sp gets a two parameters @madeByUserId and @reportedByUserId. I want to have something like:

 select * from table
  where MadeByUserId = @madeByUserId (if(@reportedByUserID != null) and ReportedByUserID = @reportedByUserID)

Basically I want to make a case in the where clause to include another filter condition based of the null/not null state of the @reportedByUserId

Is that possible?

Thanks a lot, Radu

Answer 1

You could use COALESCE.

SELECT  * 
FROM    Table
WHERE   MadeByUserId = @madeByUserId 
        AND ReportedByUserID = COALESCE(@reportedByUserID, ReportedByUserID)

This translates to

 if @reportedByUserID is `NOT NULL` then 
   compare ReportedByUserID with @reportedByUserID
 else
   compare ReportedByUserID with ReportedByUserID

From MSDN

COALESCE

Returns the first nonnull expression among its arguments.

Answer 2

Try:

 select * from table
 where MadeByUserId = @madeByUserId 
 AND (@reportedByUserID IS null OR ReportedByUserID = @reportedByUserID)

Answer 3

Add an if statement

IF (@reportedByUserId IS NOT NULL)

SELECT * FROM table t WHERE t.MadeByUserId = madeByUserId etc

Answer 4

I think this will give you what you're after.

IF (@reportedByUser IS NULL)
    select * from table 
    where MadeByUserId = @madeByUserId
ELSE
    select * from table 
    where MadeByUserId = @madeByUserId and ReportedByUserID = @reportedByUserID)