5

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

Alex
  • 3,730
  • 9
  • 43
  • 94
  • Possible duplicate of [MySQL: selecting rows where a column is null](https://stackoverflow.com/questions/3536670/mysql-selecting-rows-where-a-column-is-null) –  Jan 31 '19 at 09:52

6 Answers6

3

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);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • am I correct in saying that if '@UserId' is null this will ignore this where clause altogether? – Alex Jan 31 '19 at 10:02
  • It doesn't "ignore" the `WHERE` no @Alex , however, the part `@UserId IS NULL` will evaluate to TRUE if `@UserID` has a value of `NULL`. As either `userId = @UserId` **`OR`** `@UserId IS NULL` needs to evaluate to TRUE, all rows would be returned when `@UserID` has a value of `NULL`. – Thom A Jan 31 '19 at 10:12
  • if there is a table of 3 entries, with userId of 1,2 and 3 the variable '@UserId' IS NULL, am I correct in saying that all 3 rows would be returned by this where clause? – Alex Jan 31 '19 at 10:14
  • Why don't you test @Alex ? The syntax here is quite simple as well; is there a specific part you don't understand? (I've only added `OR` and `IS NULL`, so which of those are you having trouble with?) – Thom A Jan 31 '19 at 10:17
  • My apologies, I should have done that. I have now run it and it is the case that this will bring back all records. I need to it only bring back records where userId is null when '@UserId' is null – Alex Jan 31 '19 at 10:21
  • That wasn't clear in your qusetion, @Alex, as you can see, most answers have assumed the opposite... Updated. – Thom A Jan 31 '19 at 10:22
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)
PSK
  • 17,547
  • 5
  • 32
  • 43
  • Use COALESCE ahead of ISNULL. Many reasons, main one in my view is that COALESCE is ANSI standard, ISNULL isn't. Some supporting discussions here: https://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condition or https://blogs.msdn.microsoft.com/sqltips/2008/06/26/differences-between-isnull-and-coalesce/ – ChrisCarroll Jan 31 '19 at 10:13
  • Thanks @ChrisCarroll, I will go through the link, but i was in a impression that for a single condition match isnull is better. – PSK Jan 31 '19 at 10:19
  • 1
    I don't support `COALESCE()` for this purpose. I makes assumptions about values in the data. – Gordon Linoff Jan 31 '19 at 12:10
  • It also makes the query non-SARGable; meaning performance will be poorer and only get worse as the table `Users` grows. – Thom A Jan 31 '19 at 12:11
  • @GordonLinoff, thanks for letting me know this, i have updated the answer appropriately. – PSK Jan 31 '19 at 12:19
  • @Larnu, thanks for the feedback, i have updated the answer appropriately. – PSK Jan 31 '19 at 12:20
1

use coalesce

SELECT *
FROM Users
WHERE userId = coalesce(@UserId,val)
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

You can simplify the Boolean logic instead : 

WHERE (@UserId IS NULL OR userId = @UserId)
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • am I correct in saying that if '@UserId' is null this will ignore this where clause altogether? – Alex Jan 31 '19 at 10:02
0

Try this

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

SELECT *
FROM Users
WHERE userId= (case when @UserId is null then userId else @UserId end)
Anoos
  • 186
  • 7
0

I haven't seen the use of INTERSECT suggested so far so putting this out there as an alternative that also results in easy to read SQL when your list of potentially NULL variables might be long.

SELECT *
FROM Users
WHERE EXISTS (
   SELECT UserId
   INTERSECT SELECT @UserId
)

This pattern is useful when you have other variables to check e.g.

SELECT *
FROM Users
WHERE EXISTS (
   SELECT 
      UserId,
      Username,
      UserRole
   INTERSECT SELECT 
      @UserId,
      @Username,
      @UserRole
)
Neil Alderson
  • 35
  • 7