SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Annoying NULLs


Annoying NULLs

Author
Message
Jesse McLain
Jesse McLain
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 567
Comments posted to this topic are about the item Annoying NULLs

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Balachandra
Balachandra
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2430 Visits: 222
Hi All

I tried the query with all the given option. All the option returns the NULL value.
Ol'SureHand
Ol'SureHand
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 720
Hi guys,
MSDN might be right about the subselect scalar query, but I cannot PRINT the result as NULL .... it's just empty!

Regardless, none of the multiple choices listed BOTH SET @val=NULL and the subquery . . . so I guess all the 74% of us who correctly chose the first answer should get their bragging points ...

April First is far gone too . . .

All the best!
David in .AU
David in .AU
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 561
i wouldnt use PRINT to test it, it doesnt handle NULL's well.

And dont run every option in the same pass, it will muck up your results. If you want to test try this:


-- The declarations
DECLARE @val int;
SET @val = -1
CREATE TABLE #empty (val int)

-- The Tests
set @val = NULL
--SELECT @val = NULL FROM #empty
--SELECT @val = val FROM #empty
--SELECT @val = (SELECT val FROM #empty)

-- You need this because the previous lines only set the values, they won't print them
select @val

-- Tidy up the temp table
drop table #empty


just uncomment the line you want to test with.
You could just create the temp table once and then declare your variables etc, but I didnt think efficiency was necessary for this, so I just copied and pasted Smile

And NULL's can be cool, they just need some understanding, although that list keeps growing as you start exploring more and more functionality that SQL Server has to offer.

Admittedly I tried to answer it prior to testing and got it wrong Tongue
You live and learn...

O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice Smile

-d
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11354 Visits: 12006
David B (4/6/2009)
O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice Smile


Are you sure? I just replied to the question, and had no trouble checking both the options I expected to be correct.

It is of course not impossible that Steve fixed it between the time of your post and now, but not likely, considering that it's somewhere in the middle of the night in his part of the world. He usually fixes QotD issues when it's in the afternoon in my part of the world (Europe), about six hours from now....


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ol'SureHand
Ol'SureHand
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 720
Hugo Kornelis (4/6/2009)
David B (4/6/2009)
O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice Smile


Are you sure? I just replied to the question, and had no trouble checking both the options I expected to be correct.

It is of course not impossible that Steve fixed it between the time of your post and now, but not likely, considering that it's somewhere in the middle of the night in his part of the world. He usually fixes QotD issues when it's in the afternoon in my part of the world (Europe), about six hours from now....


OOPS! Did not even consider you could tick more than one box . . . my mistake!
So there go 2 lost bragging points . . . ;-(
Thanks for the lesson, though!
David in .AU
David in .AU
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 561
that's what I meant Smile

i probably shouldnt have used the term multiple choice by itself since that isnt implicit enough.

I did mean it wasnt a single answer multiple choice.

aah well Smile

could be worse, i could be abusing people on the nature of cursors and their requirement in everything TSQL... No, no, on second thoughts lets not start that thread again Wink
Aaron N. Cutshall
Aaron N. Cutshall
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1911 Visits: 1042
I also got this wrong because I thought that the second choice would also result in a null, so I expanded on the code a bit to test each condition:
DECLARE @val int; 
CREATE TABLE #empty (val int);

SET @val = -1;
SET @val = NULL;
IF @val IS NULL PRINT 'A: NULL';

SET @val = -1;
SELECT @val = NULL FROM #empty;
IF @val IS NULL PRINT 'B: NULL';

SET @val = -1;
SELECT @val = val FROM #empty;
IF @val IS NULL PRINT 'C: NULL';

SET @val = -1;
SELECT @val = (SELECT val FROM #empty);
IF @val IS NULL PRINT 'D: NULL';

DROP TABLE #empty;


Give it a shot and you'll see the same results:

A: NULL
D: NULL




"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
StarNamer
StarNamer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1700 Visits: 1992
Balachandra (4/5/2009)
Hi All

I tried the query with all the given option. All the option returns the NULL value.
You need to make sure that @val has been set to a non-NULL value between each test!

Derek
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2044 Visits: 3059
Bravo!! Excelent QOD. I don't mind admitting I had wrong answers because I did learn something. And that is the point, isn't it?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search