InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 13:18:04 GMT20http://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]nits.shrivastava2006 (5/6/2009)[/b][hr]Hi All, I got null value for the below statement set @val = null but for the below statementselect @val = (select val from #empty) i gotInvalid object name '#empty'Is it i have to declare #empty first ?[/quote]The temporary table needs to be created first. Take a look at the code I posted earlier to see the declarations.Thu, 07 May 2009 06:34:32 GMTAaron N. Cutshallhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxHi All, I got null value for the below statement set @val = null but for the below statementselect @val = (select val from #empty) i gotInvalid object name '#empty'Is it i have to declare #empty first ?Wed, 06 May 2009 23:33:15 GMTnits.shrivastava2006http://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]Mauricio Morales Soto (5/1/2009)[/b][hr]All the queries should return NULL.Also I tested and the result it's the same.[/quote]You have to reset the variable to -1 before each test. See the code I posted earlier.Fri, 01 May 2009 14:47:37 GMTAaron N. Cutshallhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxAll the queries should return NULL.Also I tested and the result it's the same.Fri, 01 May 2009 14:43:27 GMTMauricio Morales Shttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]harsha.bhagat1 (4/16/2009)[/b][hr]I even tested for all the options and all came null, even adding the value to the variable.[/quote]Check out the code that I posted last week and compare it to yours.Thu, 16 Apr 2009 06:37:46 GMTAaron N. Cutshallhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxI even tested for all the options and all came null, even adding the value to the variable.Thu, 16 Apr 2009 05:19:29 GMTHBhagathttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxDon't forget to set the initial value of @val to -1 before each pass, you won't get the correct answers if you don't.-dTue, 14 Apr 2009 00:10:58 GMTDavid in .AUhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[font="Verdana"][code]Create Table #Table( val int)GoSelect val From #TableGoDeclare @val intSelect @valGoDeclare @val intSet @val = NullSelect @valGoDeclare @val intSelect @val = Null From #TableSelect @valGoDeclare @val intSelect @val = val From #TableSelect @valGoDeclare @val intSelect @val = (Select val From #Table)Select @valGoDrop Table #TableGo[/code]All the options mentioned in QoD returns NULL. So ideally speaking answer should contain all the options.Mahesh[/font]Mon, 13 Apr 2009 23:56:12 GMTMahesh Botehttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxI 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.Tue, 07 Apr 2009 09:18:22 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxExcellent QotD. Got it wrong :crying: which means I have learnt something :-) .Another little bit of the fun with Nulls learnt!Mon, 06 Apr 2009 15:11:00 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxI 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!Mon, 06 Apr 2009 10:56:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxBravo!! Excelent QOD. I don't mind admitting I had wrong answers because I did learn something. And that is the point, isn't it?Mon, 06 Apr 2009 10:54:24 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]Balachandra (4/5/2009)[/b][hr]Hi AllI tried the query with all the given option. All the option returns the NULL value.[/quote]You need to make sure that @val has been set to a non-NULL value between each test!Mon, 06 Apr 2009 09:57:56 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxI 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:[code]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;[/code]Give it a shot and you'll see the same results:[code]A: NULLD: NULL[/code]Mon, 06 Apr 2009 07:29:43 GMTAaron N. Cutshallhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxthat'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 ;)Mon, 06 Apr 2009 03:59:31 GMTDavid in .AUhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]Hugo Kornelis (4/6/2009)[/b][hr][quote][b]David B (4/6/2009)[/b][hr]O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice :)[/quote]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....[/quote]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!Mon, 06 Apr 2009 02:27:39 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspx[quote][b]David B (4/6/2009)[/b][hr]O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice :)[/quote]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....Mon, 06 Apr 2009 01:41:43 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxi 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:[quote]-- The declarationsDECLARE @val int; SET @val = -1CREATE TABLE #empty (val int)-- The Testsset @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 themselect @val-- Tidy up the temp tabledrop table #empty[/quote]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 :PYou live and learn...O' and unless Steve fixed it after the last post, it was a select list, not a multiple choice :)-dMon, 06 Apr 2009 00:53:48 GMTDavid in .AUhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxHi 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!Mon, 06 Apr 2009 00:31:08 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxHi AllI tried the query with all the given option. All the option returns the NULL value.Sun, 05 Apr 2009 23:54:18 GMTBalachandrahttp://www.sqlservercentral.com/Forums/Topic690594-1181-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/TSQL/66116/">Annoying NULLs</A>[/B]Sun, 05 Apr 2009 08:15:26 GMTJesse McLain