0

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?

Shayam
  • 27
  • 1
  • 6

2 Answers2

2

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
Community
  • 1
  • 1
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
0

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

T McKeown
  • 12,971
  • 1
  • 25
  • 32
  • Thanks @T McKeown & wewesthemenace. I tried both your suggestions however the procedure is still not returning rows where [DMO].[DriverModelName] is NULL. If the parameter @driverModelName is null then I want to include all the rows weather DMO].[DriverModelName] has a value or is null or empty. – Shayam Jul 23 '15 at 02:20
  • why don't you paste the entire where clause – T McKeown Jul 23 '15 at 12:55
  • @Felix Pamittan & McKeown, thanks a lot I got my solution from your posts. – Shayam Jul 30 '15 at 02:29