May 4, 2005 at 7:41 am
Hi - the following SQL statement does NOT return qualifying rows. (fyi, TBL_A has 3 rows w/ Col_1 = NULL)
DECLARE @DateSubmitted DATETIME
SET @DateSubmitted = NULL
SELECT * FROM TBL_A
WHERE Col_1 = CASE WHEN @DateSubmitted IS NULL THEN Col_1 ELSE @DateSubmitted END
May 4, 2005 at 7:46 am
Select * from TBL_A where (Col_1 = @DateSubmitted OR @DateSubmitted IS NULL AND Col_1 IS NULL)
Anything = null will return null so you'll never get any results unless you set AINSU_NULLS OFF :
SET ANSI_NULLS ON
GO
Select * from dbo.SysObjects where null = null
GO
SET ANSI_NULLS OFF
GO
Select * from dbo.SysObjects where null = null
May 4, 2005 at 8:18 am
I have a stored proc called BusinessRules_Common_ApplicationStatusArrayList_readarray as follows:
ALTER PROCEDURE dbo.BusinessRules_Common_ApplicationStatusArrayList_readarray
@StatusID int ,
@DateOpened datetime =NULL,
@DateSubmitted datetime =NULL,
@DatePendingNotification datetime =NULL
AS
SET NOCOUNT ON
SELECT TOP 10000
ID,
PersonID
FROM
CW_Applications
WHERE
StatusID = @StatusID AND
DateOpened <= CASE WHEN @DateOpened = NULL THEN DateOpened ELSE @DateOpened END
AND
DateSubmitted <= CASE WHEN @DateSubmitted = NULL THEN DateSubmitted ELSE @DateSubmitted END
AND
DatePendingNotification <= CASE WHEN @DatePendingNotification = NULL THEN DatePendingNotification ELSE @DatePendingNotification END
When an application is created, the DateOpened is set to current date, and the status id to 0 (zero). At this point, DateSubmitted is NULL. When executing this stored proc with Status ID and DateOpened values, this returns no rows.
For some reason, when @DateSubmitted = NULL, the expression AND DateSubmitted <= DateSubmitted does not produce the desired results.
Any suggestions?
May 4, 2005 at 8:23 am
Did you read Remi's anwser? You can't use x = NULL
You should either account for those cases independently using IS NULL or the Change the ANSI_NULLS to ON (this last part I would not recomend)
* Noel
May 4, 2005 at 8:28 am
Actually : Change the ANSI_NULLS to OFF
but I still wouldn't recommend that option... You need to learn to work with the null values.
May 4, 2005 at 8:42 am
I think this will work fine for you.
Where ...(@DateSubmitted Is NUll OR col_1 = @DateSubmitted)
May 4, 2005 at 3:54 pm
try this:
DECLARE @DateSubmitted DATETIME
SET @DateSubmitted = NULL
SELECT * FROM TBL_A
WHERE Col_1 = isnull(@DateSubmitted,col_1)
AM
May 4, 2005 at 5:12 pm
Accounting for NULL values can be tricky. Do you have a date that would make a good substitute for NULLs? For instance, if you know that DateSubmitted could never be earlier than '1970-01-01 00:00:00.000', you could do something like the following (which would also work for DateOpened and DatePendingNotification):
ALTER PROCEDURE dbo.BusinessRules_Common_ApplicationStatusArrayList_readarray
.
.
.
AS
SET NOCOUNT ON
SET @DateSubmitted = COALESCE(@DateSubmitted, '1970-01-01 00:00:00.000')
SELECT TOP 10000
ID,
PersonID
FROM
CW_Applications
WHERE
.
.
.
AND
COALESCE(DateSubmitted, '1970-01-01 00:00:00.000') <= @DateSubmitted
AND ...
-Blake
May 4, 2005 at 5:45 pm
I don't think this will work when col_1 is null and @DateSubmitted is null. In such a case you will be left with the expression
Where Null = NUll and these rows will fail to be returned.
May 5, 2005 at 11:39 am
Here's another way:
SELECT TOP 10000
ID,
PersonID
FROM
CW_Applications
WHERE StatusID = @StatusID
AND DateOpened <= CASE
WHEN @DateOpened = NULL THEN DateOpened
ELSE @DateOpened
END
AND (
DateSubmitted <= @DateSubmitted
OR DateSubmitted IS NULL
)
AND DatePendingNotification <= CASE
WHEN @DatePendingNotification = NULL THEN DatePendingNotification
ELSE @DatePendingNotification
END
May 6, 2005 at 6:50 am
Without assuming anything if this code is for a stored procedure consider using multiple procdures with one deciding the logic.
CREATE PROC dbo.GetDataProc
@DateSubmitted as datetime = null
AS
SET NOCOUNT ON
If @DateSubmitted IS NULL
EXEC dbo.GetAllDataProc
ELSE
EXEC dbo.GetDatedDataProc @DateSubmitted = @DateSubmitted
GO
CREATE PROC dbo.GetAllDataProc
AS
SET NOCOUNT ON
SELECT * FROM dbo.TBL_A
GO
CREATE PROC dbo.GetDatedDataProc
@DateSubmitted as datetime
AS
SET NOCOUNT ON
SELECT * FROM dbo.TBL_A WHERE Col_1 = @DateSubmitted
GO
Or you can use procedure groups to contain as one.
CREATE PROC dbo.GetDataProc;1
@DateSubmitted as datetime = null
AS
SET NOCOUNT ON
If @DateSubmitted IS NULL
EXEC dbo.GetDataProc;2
ELSE
EXEC dbo.GetDataProc;3 @DateSubmitted = @DateSubmitted
GO
CREATE PROC dbo.GetDataProc;2
AS
SET NOCOUNT ON
SELECT * FROM dbo.TBL_A
GO
CREATE PROC dbo.GetDataProc;3
@DateSubmitted as datetime
AS
SET NOCOUNT ON
SELECT * FROM dbo.TBL_A WHERE Col_1 = @DateSubmitted
GO
Either way it will control your issue better and keep a better execution plan for each option.
May 17, 2005 at 9:38 pm
the condition could read as
where isnull(col_1,'1900-01-01 00:00:00') = isnull(@DateSubmitted,'1900-01-01 00:00:00')
I only see an issue where you have this date as default/a part of the data.
AM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply