Solved

'Simple' SQL Problem selecting null dates

Posted on 2009-05-06
4
448 Views
Last Modified: 2012-05-06
Hi,
This really should be simple, but I'm having strange behaviour on the following code:
This works fine (returns 31 rows):
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2

This returns 0 rows:
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
And this also returns 0 rows (from the SQL Profiler when I run through the app)
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO

I have tried changing it to a store procedure, to no avail.  The problem is definitely with the TraceDate IS NULL  - it works without that part of the predicate. I tried TraceDate.ISNULL = 1 but it tells me 'Cannot call methods on datetime.'

Can anyone please help?  I need to run it through the app, which interprets the top code to the second version.  

Thanks!
Carol.

exec sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
DECLARE @numInstances int
EXEC	@numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
DECLARE @ResponsiblePersonId int
SET @ResponsiblePersonId = 2
DECLARE @OrganizationId int
SET @OrganizationId=1
SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)

Open in new window

0
Comment
Question by:jrepdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 24322582
try with isnull(TraceDate,1)=1 or TraceDate is null
0
 

Author Comment

by:jrepdev
ID: 24322712
Thanks.  Nice try, but that didn't work either...  It doesn't fail, just returns 0 rows instead of 31.  I could mention that this is being implemented via an .xsd using C#, not that it will help :-)

I appreciate your help,
Carol.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24322938
I hope this issue is with respect to your ANSI_NULL settings in SQL Server 2005.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Referred from http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx

If you have ANSI_NULLS ON, then use IS NULL in where condition
If you have ANSI_NULLS OFF, then use = NULL in where condition.
0
 

Author Closing Comment

by:jrepdev
ID: 31578853
Actually, my problem was much simpler - I'd transposed the values for the parameters!  However, that was a really interesting post - thank you - I didn't know that about Ansi Nulls.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question