64

What is the best way to include an input param in the WHERE clause but exclude it if it is null?

There are a number of ways I believe, but I can't seem to remember then.

Also could I use the COALESCE()? But I think this is only for SELECTing values?

Edit

To clarify, let's say a variable called @code ="1" then my where would be Where type='B' AND code = @code but if @code is null then I only want Where type='B' - notice the missing code = @code.

halfer
  • 19,824
  • 17
  • 99
  • 186
Martin
  • 23,844
  • 55
  • 201
  • 327
  • you can use WHERE COALESCE(code =@code,true) but I'm not DB guy, not sure how good is that answer :D – Andrii Plotnikov Dec 19 '16 at 14:48
  • @Sarief Thanks, but TSQL doesn't have a boolean datatype. It can only use relational operators with non-boolean values, so your suggestion unfortunately just wouldn't work. – Suncat2000 Mar 20 '19 at 12:36

7 Answers7

101

You can use IsNull

 where some_column = IsNull(@yourvariable, 'valueifnull')

EDIT:

What you described in the comment can be done like:

where (@code is null or code = @code)
Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
  • 3
    Thanks but i mean is lets say a variable called @code ="1" then my where would be "Where type='B' AND code = @code" but if @code is null then i only want "Wher type='B'" - notice the missing code = @code – Martin Nov 19 '10 at 12:37
  • 2
    for future searchers: you could use *some_column* as IsNull's second parameter, `where some_column = IsNull(@yourvariable, some_column)`, to get the all query results without filtering, if `@yourvariable` is null. – Rohim Chou Jun 09 '21 at 03:03
16

Here's another approach

SELECT * FROM Thingies WHERE ( @thingId IS NULL OR ThingID = @thingId )
Paul Alan Taylor
  • 10,474
  • 1
  • 26
  • 42
  • 2
    Regrettably, the entire expression will be evaluated. There's no short-cutting in TSQL. Your expression is correct, though. – Suncat2000 Mar 20 '19 at 12:38
11

How about

WHERE (Column1 = @Var1 OR @Var1 IS NULL)
AND (Column2 = @Var2 OR @Var2 IS NULL)
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
5

I’d like to suggest a solution which I found on another site:

SELECT * FROM Thingies 
WHERE ThingID = isnull(@ThingId,ThingID)

With this solution if the user selects null for your parameter then your query will return all the rows as the result.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
ali
  • 51
  • 1
  • 1
5

This question really helped me with a similar issue that had a few of us scratching our heads for a bit. I only write it up in case somebody else tries the same approach and cannot figure out why it does not work.

I was trying to only evaluate a part of a multipart WHERE clause if the @Parameter was not null. I tried to do this as below but always had no rows returned if @Parameter was null.

DECLARE @Parameter int = null;

SELECT  *  FROM  TABLE
WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter)

I incorrectly thought that (@Parameter is not null AND [AlternateID] = @Parameter) would simply not form part of the full WHERE clause is @Parameter was null. However it was making the entire WHERE clause return false. The remedy was to add an OR 1=1 as below:

WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter OR 1=1)

Of course the approach outlined by Ali (not enough reputation to upvote) solves this more efficiently.

WHERE   [AlternateID] is not null 
        AND [Partner_Customer_ID] = ISNULL(@Parameter, [Partner_Customer_ID])
Philip Derbyshire
  • 61
  • 1
  • 4
  • 1
    Another way to this would be: WHERE IsNull([AlternateID], 0) = Coalesce((@Parameter, [AlternateID], 0) For optimal performance, however, dynamic SQL would be the better choice, since the chance of index seek would be increased. – SteveD Jan 14 '18 at 19:17
2

You can use ISNULL(), or check for nulls explicitly as others have mentioned. This should be OK as long as you have no more than 1 or 2 optional input parameters. But if there are more parameters, this approach would be very inefficient as the indexes you create on those columns won't be used as you would expect. In such a case i would recommend you to use dynamic SQL. Here is an excellent article that explains why http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Sadhir
  • 413
  • 4
  • 13
0

I think this will help

@id 
@name

SELECT [Id],[Name] FROM [Person] 
WHERE Id = @id and ISNULL(@name, Name)

This will allow you just ignore the Name condition if it is null

Garnik Ohanjanyan
  • 1