jrepdev
asked on
'Simple' SQL Problem selecting null dates
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'@ResponsiblePerso nId int,@OrganizationId int',@ResponsiblePersonId= 1,@Organiz ationId=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'@ResponsiblePerso nId int,@OrganizationId int',@ResponsiblePersonId= 1,@Organiz ationId=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'@ResponsiblePerso nId int,@OrganizationId int',@ResponsiblePersonId= 1,@Organiz ationId=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.
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'@ResponsiblePerso
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'@ResponsiblePerso
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'@ResponsiblePerso
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)
try with isnull(TraceDate,1)=1 or TraceDate is null
ASKER
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.
I appreciate your help,
Carol.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.