SQL server Where Clause variable may be null

Question

I have the following query (SQL server):

DECLARE @UserId INT;
SET @UserId = //... set by dynamic variable

SELECT *
FROM Users
WHERE userId = @UserId

My issue is that if the @UserId is null the query will not evaluate correctly.

How can I write this query to evaluate correctly if the variable is null or not null?

EDIT:

There have been many suggestions to use the following:

WHERE (@UserId IS NULL OR userId = @UserId)

OR similar.

In this case, if there is a table of 3 entries, with userId of 1,2 and 3 the variable '@UserId' IS NULL, this query will return all 3 entries. What I actually need it to return is no entries, as none of them have a userId of NULL

Answer 1

You need to use an OR:

DECLARE @UserId INT;
SET @UserId = //... set by dynamic variable

SELECT *
FROM Users
WHERE (userId = @UserId OR @UserId IS NULL);

This, however, could well have (severe) performance issues if you're writing this in a Stored Procedure, reusing this code a lot or adding more NULLable parameters. If so, include OPTION (RECOMPILE) in your query so that the query plan is generated each time it's run. This will stop the Data Engine using query plans generated that had a different set of NULL parameters.

Edit: The OP wasn't clear on their question. They don't want to pass the value NULL for @UserID and return all rows, they want to pass NULL and get rows where UserID has a value of NULL. That would be:

SELECT *
FROM Users
WHERE UserID = @UserID
   OR (UserID IS NULL AND @UserID IS NULL);

Answer 2

After reading the edit, i think you want your query like

SELECT *
FROM Users
WHERE COALESCE(userId ,0) = COALESCE(@UserId,0)  

Edit:

As pointed by Gordon Linoff & Larnu that above query will not be good in terms of performance as the query is "non-SARGable", for the better performance same query can be written as

  SELECT *
    FROM Users
    WHERE userId = @UserId OR( userId is null and @UserId is null)

Answer 3

use coalesce

SELECT *
FROM Users
WHERE userId = coalesce(@UserId,val)