Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SELECT setter Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 4:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
GAH! I don't really know what I was thinking when I answered. Obvious answer, but I obviously got it wrong. *grumble*
Post #864395
Posted Friday, February 12, 2010 1:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:00 AM
Points: 3,015, Visits: 912
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.
Post #864478
Posted Friday, February 12, 2010 9:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 1,309, Visits: 1,684
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.
Post #864702
Posted Friday, February 12, 2010 11:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:53 PM
Points: 1,521, Visits: 3,039
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.
Post #864808
Posted Wednesday, February 17, 2010 5:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 8,726, Visits: 9,277
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
Post #867623
Posted Thursday, February 18, 2010 12:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:00 AM
Points: 3,015, Visits: 912
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.
Post #867780
Posted Tuesday, March 30, 2010 8:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 PM
Points: 11,194, Visits: 11,141
This difficult-to-recall behaviour is the main reason I prefer SET @x = (SELECT...) syntax.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892868
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse