SELECT @local_variable

  • Comments posted to this topic are about the item SELECT @local_variable

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • Nice question - I did not see that coming!

    _____________________________________________________________________
    MCSA SQL Server 2012

  • This was removed by the editor as SPAM

  • Good question and something to keep in mind always.

    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    ---------------
    Mel. 😎

  • SqlMel (7/29/2015)


    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    I keep using Top 1 clause in assigning variables when I know that my select output is same value

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • SqlMel (7/29/2015)


    Good question and something to keep in mind always.

    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    This could mask a problem if something leads to more than one row being returned.

    An alternative might be something like

    SET @X = (SELECT Value FROM #Values WHERE ....)

    This would fail if more than one row is returned, rather than potentially assigning an erroneous value.

  • Finally an nice easy one, thanks.

  • SqlMel (7/29/2015)


    Good question and something to keep in mind always.

    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉

    Great question Naveen.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Great question, indeed. Thanks, Naveen!

  • Was an easy one. Thnx for the question.

    Thanks.

  • Interesting question. Works in current versions but shows bad practise.

    Every SQL Server version I have known would do this, but in theory the value returned could be any row of the table - the optimiser could detect that no row-set is returned by the first select so as soon as the engine reads one value it can use that for the assignment. Even if a rowset were returned, the the rowset could be sorted as the last action of the select statement, ie after the assignment, so not assigning the value from last row according to the ORDER BY clause.

    So (at least in theory) it's bad practice to rely on getting 1 here. A clever optimser would make the first select assign 2 (the first row and only selected) and discard the ORDER BY clause. That's what people keep on telling me might happen, and I regard it as a failure of the language definition that meaningless ORDER BY clauses are note precluded by the syntax definition, and I haven't seen any argument that an order clause like this one is meaningful.

    Personally I think that for simple cases like this one it would be far more sensible (and deliver better perormance) to write "SELECT @x = MIN(Value) FROM #values" instead of something with a meaningless and potentially ineffective order by clause.

    Tom

  • Sean Lange (7/29/2015)


    Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉

    I agree Sean, and this is a way to pick the first value instead of default last one.

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • I mostly do this to read parameter values that I have set up in tables so in all the cases I can think of I only get returned one row for the query because the syntax is mostly like:

    select @value = value from parameter_table where parameter_id = 999

    sestell1 (7/29/2015)


    SqlMel (7/29/2015)


    Good question and something to keep in mind always.

    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    This could mask a problem if something leads to more than one row being returned.

    An alternative might be something like

    SET @X = (SELECT Value FROM #Values WHERE ....)

    This would fail if more than one row is returned, rather than potentially assigning an erroneous value.

    ---------------
    Mel. 😎

  • Of course. I learned that lesson a looooong time ago 😀

    Sean Lange (7/29/2015)


    SqlMel (7/29/2015)


    Good question and something to keep in mind always.

    I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

    Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉

    Great question Naveen.

    ---------------
    Mel. 😎

  • ...Even if a rowset were returned, the the rowset could be sorted as the last action of the select statement, ie after the assignment, so not assigning the value from last row according to the ORDER BY clause.

    VERY interesting point. I had not thougt about this.

    ---------------
    Mel. 😎

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

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