Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Annoying NULLs Expand / Collapse
Author
Message
Posted Sunday, April 5, 2009 8:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 15, 2014 12:28 PM
Points: 305, Visits: 565
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
Posted Sunday, April 5, 2009 11:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:35 AM
Points: 2,185, Visits: 194
Hi All

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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:12 AM
Points: 370, Visits: 715
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
Posted Monday, April 6, 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: Thursday, August 21, 2014 6:09 PM
Points: 522, Visits: 554
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
Posted Monday, April 6, 2009 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 5,975, Visits: 8,233
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
Posted Monday, April 6, 2009 2:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:12 AM
Points: 370, Visits: 715
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
Posted Monday, April 6, 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: Thursday, August 21, 2014 6:09 PM
Points: 522, Visits: 554
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
Posted Monday, April 6, 2009 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:48 AM
Points: 1,111, Visits: 542
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
Posted Monday, April 6, 2009 9:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Posted Monday, April 6, 2009 10:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:17 PM
Points: 1,521, Visits: 3,038
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 123»»»

Permissions Expand / Collapse