SELECT setter

  • stoklosa

    Old Hand

    Points: 367

    Comments posted to this topic are about the item SELECT setter

  • This was removed by the editor as SPAM

  • Fal

    SSCrazy

    Points: 2873

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

    S.

  • SQLRNNR

    SSC Guru

    Points: 281205

    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: 1842

    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.

  • This was removed by the editor as SPAM

  • Open Minded

    SSCommitted

    Points: 1842

    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.

  • This was removed by the editor as SPAM

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

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

  • john.arnott

    SSChampion

    Points: 11882

    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

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

  • Rune Bivrin

    SSCertifiable

    Points: 7614

    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: 12241

    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

    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: 104772

    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

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

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