November 21, 2007 at 8:15 am
Consider the following:
DECLARE @Datum datetime
--SELECT @Datum = getdate()
SELECT @Datum AS RealDate,
CASE @Datum
WHEN NULL THEN 'NULL'
ELSE 'NOT NULL'
END
AS NULLed
SELECT @Datum AS RealDate,
ISNULL(@Datum,NULL) AS NULLed
I wish to find out if a column has a NULL value and do something based on TRUE or FALSE. The ISNULL function gives a correct result, but the CASE statement does not! If I give @Datum a value, ISNULL works correctly, but CASE just gives me the same value...
I'm thinking of writing my own fucntion, but I am amazed why the CASE statement does not give me the correct value. Can anybody enlighten me? Any hints?
Greetz,
Hans Brouwer
November 21, 2007 at 8:24 am
Its not the CASE statement but because of the NULL. There are lot of differences between NULL and IS NULL.
Here are some of nice articles about the differences...
http://qa.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/
http://qa.sqlservercentral.com/articles/Advanced+Querying/2829/
DECLARE @Datum datetime
--SELECT @Datum = getdate()
SELECT @Datum AS RealDate,
CASE @Datum
WHEN NULL THEN 'NULL'
ELSE 'NOT NULL'
END
AS NULLed,
CASE WHEN @Datum IS NULL THEN 'NULL'
ELSE 'NOT NULL'
END
AS IsNulled
SELECT @Datum AS RealDate,
ISNULL(@Datum,NULL) AS NULLed
--Ramesh
November 21, 2007 at 8:28 am
You can get around this by changing the way you test for NULL. By using "IS NULL" you'll get the desired results.
I can't speak to the "WHY" the other didn't work, but perhaps someone else on here can.
DECLARE @Datum datetime
--SELECT @Datum = getdate()
SELECT
@Datum AS RealDate,
CASE WHEN @Datum IS NULL
THEN 'NULL'
ELSE 'NOT NULL'
END
AS CASENULL
SELECT
@Datum AS RealDate,
ISNULL(@Datum,NULL) AS [ISNULL]
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2007 at 8:29 am
*laughs* It seems Ramesh and I were working on the same solution at the same time. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2007 at 8:32 am
May be not, its too late in the day:D
--Ramesh
November 21, 2007 at 8:52 am
Tnx guys,
I tried IS NULL but I guess I did something wrong there. This works & helps. Tnx again.
Greetz,
Hans Brouwer
November 21, 2007 at 9:43 am
Just in case anyone is interested, the reason that When Null did not work is because SQL tried to use an equality operator to compare the values. Null is the absence of a value, therefor it cannot be equal to anything.;)
November 21, 2007 at 1:48 pm
DDarn! Rich beat me to the punch! :w00t:
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 21, 2007 at 2:31 pm
David Paskiet (11/21/2007)
DDarn! Rich beat me to the punch! :w00t:
Close David, you were only 4 hours off :Whistling:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply