IS NULL vs = NULL in where clause + SQL Server

Question

Hello

How to check a value IS NULL [or] = @param (where @param is null)

Ex:

Select column1 from Table1
where column2 IS NULL => works fine

If I want to replace comparing value (IS NULL) with @param. How can this be done

Select column1 from Table1
where column2 = @param => this works fine until @param got some value in it and if is null never finds a record.

How can this achieve?

Answer 1

select column1 from Table1
  where (@param is null and column2 is null)
     or (column2 = @param)

Answer 2

There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyond just making the query return the proper answer, no matter how slow it is, look at this article on Dynamic Search Conditions in T-SQLby Erland Sommarskog

here is a link to the portion on x = @x OR @x IS NULL