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

Question

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)

How about

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

Here's another approach

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

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.

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

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/