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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy