April 5, 2009 at 8:15 am
Comments posted to this topic are about the item Annoying NULLs
April 5, 2009 at 11:54 pm
Hi All
I tried the query with all the given option. All the option returns the NULL value.
Balachandra Srinevasalu
April 6, 2009 at 12:31 am
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!
April 6, 2009 at 12:53 am
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 😛
You live and learn...
O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice 🙂
-d
April 6, 2009 at 1:41 am
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....
April 6, 2009 at 2:27 am
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!
April 6, 2009 at 3:59 am
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 😉
April 6, 2009 at 7:29 am
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
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
April 6, 2009 at 10:54 am
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?
April 6, 2009 at 10:56 am
I checked this on 2005, and both answers marked as correct (A and D) work.
You have to run each SET/SELECT and then a SELECT @val for each answer, with separate executions. I added a drop table #empty at the end as well.
The question has been edited to say "select all that apply"
and I'm awake, Hugo!
April 6, 2009 at 3:11 pm
Excellent QotD. Got it wrong :crying: which means I have learnt something 🙂 .
Another little bit of the fun with Nulls learnt!
April 7, 2009 at 9:18 am
I doubt if I can one day master the use of " NULL ". No matter how much I try to understand it, there always something left.
Well, " NULL " value handling is most of time are pain in the B**t.
very good QOD.
SQL DBA.
April 13, 2009 at 11:56 pm
[font="Verdana"]
Create Table #Table
(
valint
)
Go
Select val From #Table
Go
Declare @valint
Select @val
Go
Declare @valint
Set @val = Null
Select @val
Go
Declare @valint
Select@val = Null From #Table
Select@val
Go
Declare @valint
Select@val = val From #Table
Select@val
Go
Declare @valint
Select@val = (Select val From #Table)
Select@val
Go
Drop Table #Table
Go
All the options mentioned in QoD returns NULL. So ideally speaking answer should contain all the options.
Mahesh[/font]
MH-09-AM-8694
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply