TSQL Case in where statement if parameter is null
10,585
Solution 1
Try:
select * from table
where MadeByUserId = @madeByUserId
AND (@reportedByUserID IS null OR ReportedByUserID = @reportedByUserID)
Solution 2
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.
Author by
Radu D
Updated on June 07, 2022Comments
-
Radu D about 2 years
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