CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shayam
Shayam

Reputation: 27

SQL CASE query condition

I have the following condition in my stored procedure:

[DMO].[DriverModelName] =
   CASE WHEN ISNULL(@driverModelName,'NotSet') = 'NotSet' THEN 
[DMO].[DriverModelName]
   ELSE
@driverModelName
    END

This implies that when I pass 'NotSet' to varchar parameter @driverModelName, it should return all the rows but for some reason it's returning only those rows which has a value in column DriverModelName & omitting the null value rows.

Am I doing anything wrong here?

Upvotes: 0

Views: 67

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is because NULL == NULL = FALSE, for the purpose of the WHERE clause, unless you set ANSI_NULLS to OFF. Example: 

SET ANSI_NULLS OFF

IF NULL = NULL
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

SET ANSI_NULLS ON

IF NULL = NULL
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

The result is:

TRUE
FALSE

In order to get all rows, including the NULL values, you should use

(@driverModelName IS NULL OR [DMO].[DriverModelName] = @driverModelName)

For references:

  1. http://www.sqlservercentral.com/blogs/steve_jones/2010/10/13/common-sql-server-mistakes-1320-equals-null/
  2. SQL is null and = null
  3. Why does NULL = NULL evaluate to false in SQL server

Additional Reading on Catch-All queries:

  1. Catch-all Queries by Gail Shaw
  2. The Curse and Blessings of Dynamic SQL by Erland Sommarskog

Upvotes: 2

T McKeown
T McKeown

Reputation: 12847

Well if [DMO].[DriverModelName] is ever NULL what are you expecting the result of this CASE be? Isn't this missing a ISNULL() around that column too?

ISNULL([DMO].[DriverModelName], 'NotSet') =
   CASE WHEN ISNULL(@driverModelName,'NotSet') = 'NotSet' THEN 
[DMO].[DriverModelName]
   ELSE
   @driverModelName
END

You cant say NULL = @someValue, it must be IS NULL or handle both sides with ISNULL()

Upvotes: 0

Related Questions