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

How about

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

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)

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.

Here's another approach

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

Related