CODEWITHSUNDEEP
sqlsql-servert-sql
Sreedhar
Sreedhar

Reputation: 30045

IS NULL vs = NULL in where clause + SQL Server

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?

Upvotes: 20

Views: 76123

Answers (5)

Dan
Dan

Reputation: 1333

I realize this is an old question, but I had the same one, and came up with another (shorter) answer. Note: this may only work for MS SQL Server, which supports ISNULL(expr,replacement).

SELECT column1 FROM table1
WHERE ISNULL(column2,'') = ISNULL(@param,'')

This also assumes you treat NULL and empty strings the same way.

Upvotes: 6

Mike A
Mike A

Reputation: 51

Select column1 from Table1
where (column2 IS NULL and @param IS NULL) 
or ( column2 IS NOT NULL AND @param IS NOT NULL AND ( column2 = @param )  )

Upvotes: 0

Diego Mendes
Diego Mendes

Reputation: 11361

WHERE ((COLUMN1 = @PARAM) OR (COLUMN1 IS NULL AND @PARAM IS NULL))

Upvotes: 1

KM.
KM.

Reputation: 103707

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

Upvotes: 2

Laurence Gonsalves
Laurence Gonsalves

Reputation: 143344

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

Upvotes: 42

Related Questions