SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT setter


SELECT setter

Author
Message
FargoUT
FargoUT
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 312
GAH! I don't really know what I was thinking when I answered. Obvious answer, but I obviously got it wrong. *grumble*
Rune Bivrin
Rune Bivrin
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4536 Visits: 1573
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
sknox
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5298 Visits: 3005
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
john.arnott
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 3059
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.
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36177 Visits: 12793
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

Rune Bivrin
Rune Bivrin
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4536 Visits: 1573
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.


Just because you're right doesn't mean everybody else is wrong.
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54788 Visits: 11392
This difficult-to-recall behaviour is the main reason I prefer SET @x = (SELECT...) syntax.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search