SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jaroslaw Stoklosa / SELECT setter / Latest Posts

RE: SELECT setter

Paul White — Tue, 30 Mar 2010 08:49:42 GMT

This difficult-to-recall behaviour is the main reason I prefer SET @x = (SELECT...) syntax.

RE: SELECT setter

Rune Bivrin — Thu, 18 Feb 2010 00:26:01 GMT

Oh, I know that. From the perspective of someone who started with SQL Server way back in 91 it's completely clear. But for the less experienced it can certainly appear strange and unexpected.

RE: SELECT setter

L' Eomot Inversé — Wed, 17 Feb 2010 17:01:33 GMT

[quote][b]Rune Bivrin (2/12/2010)[/b][hr]I got it right because I knew it, but I understand why that might not be obvious, and the behaviour is slightly iffy.If you do [code]SELECT @var = MAX(object_id) FROM sys.objectsWHERE 1=0[/code]you'll get NULL assigned, but[code]SELECT @var = object_idFROM sys.objectsWHERE 1=0[/code]will retain the previous value of @var.It's really a source for unexpected behaviour when a WHERE-clause is a little wrong, and relying on it when coding isn't really fair to whoever will maintain your code.[/quote]It isn't really iffy, and it's the behaviour which you would naturally expect if you looked at the select - you just have to consider what the raw select (without the local variable assignment) would return.[code]SELECT max(object_id) from sys.objects where 1=0[/code] returns NULL because applying max to an empty set produces NULL. So [code]SELECT @var=max(object_id) from sys.objects where 1=0[/code] sets @var to NULL. On the other hand, [code]SELECT object_id from sys.objects where 1=0[/code] doesn't return anything, so [code]SELECT @var = object_id from sys.objects where 1=0[/code] doesn't have any value (not even null) to assign to @var and so it doesn't assign anything.

RE: SELECT setter

john.arnott — Fri, 12 Feb 2010 11:21:55 GMT

sknox,[quote]What difference does it make? At the end of the day, answering a good QotD correctly only gives you two things:1. A better understanding of SQL / SQL Server.2. An increase to your SSC points.. . .So, those people who just do the copy-and-paste are actually cheating themselves out of better knowledge for a few measley points. Let them.[/quote]Exactly. The difference is that it's frustrating to think that people are cheating themselves for the sake of the stupid points. I put my comment inside pseudo-tags as an indication of what I meant to be wry sarcasm. I think we agree on the basic issue. I would hope that the points-hawks read your post and take it to heart.

RE: SELECT setter

sknox — Fri, 12 Feb 2010 09:08:25 GMT

[quote][b]john.arnott (2/11/2010)[/b][hr] <rant on> I can't imagine that 85% of respondants actualy knew the result of the SELECTwhere the condition is false would not be assigned to the variable. </rant>[/quote]What difference does it make? At the end of the day, answering a good QotD correctly only gives you two things:1. A better understanding of SQL / SQL Server.2. An increase to your SSC points.The better understanding of SQL / SQL Server comes when you either think the question through or research it out. It does not come from simply copying and pasting into SSMS -- that only tells you what happens, not how or why.The SSC points are cute, and can be a fun little game to play with your fellow SSCers, but they have no intrinsic value beyond this site.So, those people who just do the copy-and-paste are actually cheating themselves out of better knowledge for a few measley points. Let them. For the record, I got this one correct because I've seen this before.

RE: SELECT setter

Rune Bivrin — Fri, 12 Feb 2010 01:02:11 GMT

I got it right because I knew it, but I understand why that might not be obvious, and the behaviour is slightly iffy.If you do [code]SELECT @var = MAX(object_id) FROM sys.objectsWHERE 1=0[/code]you'll get NULL assigned, but[code]SELECT @var = object_idFROM sys.objectsWHERE 1=0[/code]will retain the previous value of @var.It's really a source for unexpected behaviour when a WHERE-clause is a little wrong, and relying on it when coding isn't really fair to whoever will maintain your code.

RE: SELECT setter

FargoUT — Thu, 11 Feb 2010 16:28:21 GMT

GAH! I don't really know what I was thinking when I answered. Obvious answer, but I obviously got it wrong. *grumble*

RE: SELECT setter

john.arnott — Thu, 11 Feb 2010 11:16:30 GMT

I'm proud of my wrong answer ("NULL") as it shows I thought it through, even if incorrectly, before triying the script in SSMS. <rant on> I can't imagine that 85% of respondants actualy knew the result of the SELECT .... where the condition is false would not be assigned to the variable. </rant>Meanwhile, for those interested in seeing this behavior in SSMS....[code="sql"]DECLARE @i INTSELECT @i = 0SELECT @i AS i --Returns 0SELECT @i = 1 where 1=0 --Null; assignment ignoredSELECT @i AS i --Returns 0SELECT @i = (SELECT 2 where 1=0)--Assign result of subSelect with FALSE condition ***SELECT @i AS i --Returns NULLSELECT @i = 3 where 1=1 --Condition is true, so assignment worksSELECT @i AS i --Returns 3SELECT @i = (SELECT 4 where 1=1)--Assign result of subSelect with TRUE condition ***SELECT @i AS i --Returns 4[/code]

RE: SELECT setter

Steve Jones - SSC Editor — Thu, 11 Feb 2010 10:30:48 GMT

I suspected no assignment, but I had these visions of C where = means something happens.

RE: SELECT setter

Stewart "Arturius" Campbell — Thu, 11 Feb 2010 01:40:43 GMT

That is correct.Wherever the predicate equates to true, an action will take place.

RE: SELECT setter

Open Minded — Thu, 11 Feb 2010 00:12:21 GMT

Thanks! I hope I got this right: if the WHERE clause will create a result or boolean TRUE (1), then the SELECT part makes a change, if the SELECT part happens to be changing the value of a variable.It will be just like reversing an IF-THEN statement, like THEN-IF. Well, I hope I got that analogy right also.

RE: SELECT setter

Stewart "Arturius" Campbell — Thu, 11 Feb 2010 00:02:25 GMT

[quote]If a 'A SELECT statement that doesn't return results doesn't set a variable to anything.', then why didn't it remain at 1 when I changed the logic to 'where 1=1' ?[/quote]if you run the following statements:[code="sql"]select 'Has no value' where 1 = 0select 'Has value' where 1 = 1[/code]you will see that the first statement does not return any rows, but the second does.in this vein, the question posted has "where 1 = 0", which does not generate a result set, which will not change the variable's value.

RE: SELECT setter

Open Minded — Wed, 10 Feb 2010 23:46:13 GMT

I got the answer right because I was looking at the logic, 'where 1=0'.But when I changed it to 'where 1=1', the output came out as 0.If a 'A SELECT statement that doesn't return results doesn't set a variable to anything.', then why didn't it remain at 1 when I changed the logic to 'where 1=1' ?I hope I don't sound terrible naive about this. Below is the code I used to check the veracity of the statement:DECLARE @i INTSELECT @i = 1SELECT @i = 0 where 1=0SELECT @i as iSELECT @i = 1SELECT @i = 0 where 1=1SELECT @i as iSELECT @iMay I ask for further enlightenment? Thank you.

RE: SELECT setter

SQLRNNR — Wed, 10 Feb 2010 23:18:35 GMT

Agreed. Good back to basics question.Thanks

RE: SELECT setter

Fal — Wed, 10 Feb 2010 23:07:44 GMT

A good question to remind us of something we all might forget: WHERE isn't just used after FROM.S.

RE: SELECT setter

Stewart "Arturius" Campbell — Wed, 10 Feb 2010 22:25:16 GMT

Usig SELECT is a versatile method of allocating values to multiple variables as well.good "back to basics" question.

SELECT setter

stoklosa — Wed, 10 Feb 2010 21:17:03 GMT

Comments posted to this topic are about the item [B]SELECT setter[/B]