Checking an input param if not Null and using it in where in SQL Server

Question

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.

Solution 1

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)

Solution 2

Here's another approach

SELECT * FROM Thingies WHERE ( @thingId IS NULL OR ThingID = @thingId )

Solution 3

How about

WHERE (Column1 = @Var1 OR @Var1 IS NULL)
AND (Column2 = @Var2 OR @Var2 IS NULL)

Solution 4

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.

Solution 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])    

View more solutions

Comments

[Martin]

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

[SteveD]

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.

[Suncat2000]

Regrettably, the entire expression will be evaluated. There's no short-cutting in TSQL. Your expression is correct, though.

[Rohim Chou]

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.