Storing a variable with a SELECT, and why it didn't work.

  • Kenneth.Fisher

    SSCoach

    Points: 19577

    Comments posted to this topic are about the item Storing a variable with a SELECT, and why it didn't work.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • RonKyle

    SSC-Dedicated

    Points: 31457

    I use an @@ROWCOUNT after my use of SELECTs to populate a variable.  IF @@ROWCOUNT=0 then do something.  I think I like that better than prepopulating, but it might be six of one, half dozen of the other.

  • WolfgangE

    SSCertifiable

    Points: 6311

    Hello there,

    this is a nice article, showing the danger of this kind of SQL code.
    Actually there are many people not knowing the detailled behaviour of this code, especially the part where the select returns no rows. It's kind of logical, we just are not aware of it when using it.

    I personally try to avoid this code at all, mainly for 2 reasons:
    1) unexpected behaviour, as this article is about
    2) performance: if the select is running against a huge table it really makes difference

    If I have to assign only 1 variable I use a subselect, as it really assigns null to the variable when no row is returned, or an exception, if more than one is returned (an exception I want to have to get to know of the situation).

    If I have to deal with assigning multiple variables, my first thought is:
    Do I really need variables, or can I do it using temp tables or table variables?

    Many of us tend to not think of tables, when "needing just 2 or 3 variables". But try it, you will discover new ways of solving queries.

  • qbrt

    SSCrazy

    Points: 2422

    yap.. learned these nuances the hard way, long time ago. thanks for writing it up and sharing.

  • roger.plowman

    SSChampion

    Points: 10149

    What about:

    DECLARE @Date DATE  = COALESCE((SELECT [Some Date]
                                    FROM   [Some Table]
                                    WHERE  [Some Key] = 'Key Value'),'1900-01-01')

    This lets you declare, set, and default the variable in one statement. You don't have to worry about no rows. As for multiple rows that's way keys are for... :hehe:

  • RonKyle

    SSC-Dedicated

    Points: 31457

    You don't have to worry about no rows.

    The row count acts as a kind of error handling.  I only use these when there should be a row, but it wouldn't be inconceivable that there's not a row.  In that case, I want it to do something.  A common case--the variable is used to update a row for some ETL auditing.  But if the row count is zero, an insert statement will fire instead.  This wouldn't help because I'd still have to test for a variable.

  • Kenneth.Fisher

    SSCoach

    Points: 19577

    roger.plowman - Monday, April 2, 2018 8:46 AM

    What about:

    DECLARE @Date DATE  = COALESCE((SELECT [Some Date]
                                    FROM   [Some Table]
                                    WHERE  [Some Key] = 'Key Value'),'1900-01-01')

    This lets you declare, set, and default the variable in one statement. You don't have to worry about no rows. As for multiple rows that's way keys are for... :hehe:

    😀 Certainly you can do it that way. In fact I recently fixed some code doing exactly that.

    FYI in your code, technically you are actually initializing the variable, not setting it, just with a subselect.  You can also do it as a SET later, but again, you are using a subquery not actually setting the variable using a SELECT statement 🙂 And sometimes you just need multiple variables.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    Covering the += operator might have been a nice way of demonstrating whats happening.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-equal-transact-sql

  • RonKyle

    SSC-Dedicated

    Points: 31457

    Covering the += operator might have been a nice way of demonstrating whats [sic] happening.

    Is this better than the CONCAT function?   I use that exclusively now for string concatenation.

  • gbritton1

    SSCertifiable

    Points: 6520

    RonKyle - Tuesday, April 3, 2018 7:57 AM

    Covering the += operator might have been a nice way of demonstrating whats [sic] happening.

    Is this better than the CONCAT function?   I use that exclusively now for string concatenation.

    I use CONCAT where convenient.  Sometimes it has trouble with collations.  Try these two queries:
    select 'type: ' + type_desc from sys.indexes
    select concat('type: ', type_desc) from sys.indexes

    to see what I mean

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    RonKyle - Tuesday, April 3, 2018 7:57 AM

    Covering the += operator might have been a nice way of demonstrating whats [sic] happening.

    Is this better than the CONCAT function?   I use that exclusively now for string concatenation.

    CONCAT is a row context operation.
    STRING_AGG is a column context operation and similar in affect to what the += operator will do when used in a  select @var += query.

    However the point I made wasn't about the best or worse string concatenation methods. It's was about demonstrating to the reader what is happening when attempting a SELECT @var = on a query that returns multiple rows. Just a suggestion, that's all.

Viewing 11 posts - 1 through 11 (of 11 total)

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