Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Annoying NULLs
Add to briefcase
21 posts, Page 1 of 3
123»»»

Annoying NULLs Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jesse McLain
Posted Sunday, April 05, 2009 8:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552
Comments posted to this topic are about the item Annoying NULLs

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #690594
Balachandra
Posted Sunday, April 05, 2009 11:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 1:23 AM
Points: 2,162, Visits: 170
Hi All

I tried the query with all the given option. All the option returns the NULL value.
Post #690737
Ol'SureHand
Posted Monday, April 06, 2009 12:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:47 PM
Points: 356, Visits: 700
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!
Post #690749
David in .AU
Posted Monday, April 06, 2009 12:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543
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 :)

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 :P
You live and learn...

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

-d
Post #690763
Hugo Kornelis
Posted Monday, April 06, 2009 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 5,237, Visits: 7,044
David B (4/6/2009)
O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice :)


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
Post #690783
Ol'SureHand
Posted Monday, April 06, 2009 2:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:47 PM
Points: 356, Visits: 700
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 :)


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!
Post #690801
David in .AU
Posted Monday, April 06, 2009 3:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543
that's what I meant :)

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 :)

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 ;)
Post #690857
Aaron N. Cutshall
Posted Monday, April 06, 2009 7:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 9:57 AM
Points: 895, Visits: 504
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
Post #690999
Derek Dongray
Posted Monday, April 06, 2009 9:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946
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
Post #691192
john.arnott
Posted Monday, April 06, 2009 10:54 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
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?
Post #691260
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.