We help IT Professionals succeed at work.

'Simple' SQL Problem selecting null dates

jrepdev
jrepdev asked
on
480 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

Comment
Watch Question

Commented:
try with isnull(TraceDate,1)=1 or TraceDate is null

Author

Commented:
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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.