SELECT setter

Question

Post reply

SELECT setter

stoklosa

Old Hand

Points: 367
More actions
February 10, 2010 at 9:17 pm

#386297

Comments posted to this topic are about the item SELECT setter
February 10, 2010 at 10:25 pm

This was removed by the editor as SPAM
February 11, 2010 at 12:02 am

This was removed by the editor as SPAM
February 11, 2010 at 1:40 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Fal Hall of Fame Points: 3143 More actions · Answer 1

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

S.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Agreed. Good back to basics question.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Open Minded SSCommitted Points: 1852 More actions · Answer 3

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 INT

SELECT @i = 1

SELECT @i = 0 where 1=0

SELECT @i as i

SELECT @i = 1

SELECT @i = 0 where 1=1

SELECT @i as i

SELECT @i

May I ask for further enlightenment? Thank you.

Open Minded SSCommitted Points: 1852 More actions · Answer 4

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.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

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

john.arnott SSChampion Points: 11882 More actions · Answer 6

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....

DECLARE @i INT

SELECT @i = 0

SELECT @i AS i--Returns 0

SELECT @i = 1 where 1=0--Null; assignment ignored

SELECT @i AS i--Returns 0

SELECT @i = (SELECT 2 where 1=0)--Assign result of subSelect with FALSE condition ***

SELECT @i AS i--Returns NULL

SELECT @i =3 where 1=1--Condition is true, so assignment works

SELECT @i AS i--Returns 3

SELECT @i = (SELECT 4 where 1=1)--Assign result of subSelect with TRUE condition ***

SELECT @i AS i--Returns 4

FargoUT SSC Eights! Points: 853 More actions · Answer 7

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

Rune Bivrin SSCrazy Eights Points: 8436 More actions · Answer 8

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

SELECT @var = MAX(object_id)

FROM sys.objects

WHERE 1=0

you'll get NULL assigned, but

SELECT @var = object_id

FROM sys.objects

WHERE 1=0

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.

Just because you're right doesn't mean everybody else is wrong.

sknox SSChampion Points: 12388 More actions · Answer 9

john.arnott (2/11/2010)
<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>

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.

john.arnott SSChampion Points: 11882 More actions · Answer 10

sknox,

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.

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Rune Bivrin (2/12/2010)
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
SELECT @var = MAX(object_id)
FROM sys.objects
WHERE 1=0
you'll get NULL assigned, but
SELECT @var = object_id
FROM sys.objects
WHERE 1=0
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.

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.

SELECT max(object_id) from sys.objects where 1=0 returns NULL because applying max to an empty set produces NULL. So SELECT @var=max(object_id) from sys.objects where 1=0 sets @var to NULL. On the other hand, SELECT object_id from sys.objects where 1=0 doesn't return anything, so SELECT @var = object_id from sys.objects where 1=0 doesn't have any value (not even null) to assign to @var and so it doesn't assign anything.

Tom