A simple T-SQL statement to create a list of lookup values

  • Such a shame that the author did not include the disclaimer that says that this method is not supported and not guaranteed to return correct results. I have lost the URL, but I once saw a page with several examples where this same code returns the data in a different order (in cases where an ORDER BY is also used - not really relevant in this case), or incomplete data.

    Please use the FOR XML method instead. See the post by feeza_ibrahim (third post on page 1 of this topic)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • One of the threads where a method like this was discussed someone from MS came on and said that it relied on an undocumented feature in the query optimizer that could be changed at any time. Unfortunately, like Hugo I don't have the URL to that. The shame is that a method that's so widely used (I've seen it discussed several times) isn't well documented to be unsupported.

  • intrope (3/21/2011)


    You can also do this with two variables with one SELECT:

    DECLARE @t varchar(max)

    DECLARE @S varchar(1)

    SET @t = ''

    SET @S = ''

    SELECT @t += @S + Tag, @S = ',' FROM Tags ORDER BY Tag

    SELECT @t

    And just for fun, a recursive CTE solution! :hehe:

    DECLARE @t varchar(max);

    SET @t = '';

    WITH rectags AS

    (

    SELECT TOP 1 Tag FROM Tags ORDER BY Tag

    UNION ALL

    (

    SELECT ', ' + Tag FROM Tags

    EXCEPT

    SELECT TOP 1 ', ' + Tag FROM Tags ORDER BY Tag

    )

    )

    SELECT @t += Tag FROM rectags

    SELECT @t

    Is this close enough to what you were refering to Hugo?

    https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

  • And this the original thread that started the connect ticket.

    http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx

  • hi Hugo,

    What do you mean with "incomplete data"?

    I only found these articles:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx

    http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/t-sql-variables-multiple-value-assignment.aspx

    http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

    They only explain the obvious: When assigning a single value using the SELECT @var = column FROM table syntax assigns the last row value, which may be any one due to optimization.

  • stefan.hoffmann (3/21/2011)


    hi Hugo,

    What do you mean with "incomplete data"?

    I only found these articles:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx

    http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/t-sql-variables-multiple-value-assignment.aspx

    http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

    They only explain the obvious: When assigning a single value using the SELECT @var = column FROM table syntax assigns the last row value, which may be any one due to optimization.

    Hi Stefan,

    Thanks to the excellent links just piosted by Ninja (thanks, Ninja!), I can show you the code to demonstrate what I mean. Just copy, run, and check the results. (Note that this code is an almost verbatim copy of the code Ninja included in the Conenct item he links to a few posts up).

    PRINT 'No ORDER BY';

    PRINT '';

    DECLARE @X NVARCHAR(MAX);

    SET @X = '';

    SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY name;

    PRINT @X;

    go

    PRINT '';

    PRINT '';

    PRINT 'ORDER BY NEWID()';

    PRINT '';

    DECLARE @X NVARCHAR(MAX);

    SET @X = '';

    SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();

    PRINT @X;

    go

    PRINT '';

    PRINT '';

    PRINT 'ORDER BY SELECT(NEWID())';

    PRINT '';

    DECLARE @X NVARCHAR(MAX);

    SET @X = '';

    SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY (SELECT NEWID());

    PRINT @X;

    go

    Note that this is not the code I originally found on some website (I believe Steve Kass posted that code), but the result is similar enough - instead of all rows concatenated, you get just one of the rows. Probably the one that happens to be processed last.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • hi Hugo,

    http://msdn.microsoft.com/en-us/library/ms187953.aspx

    states that the result is defined as the value of the last row and not undefined as Umachandar in the Connect ticket says, should be corrected then.

    USE AdventureWorks2008R2 ;

    GO

    DECLARE @EmpIDVariable NVARCHAR(MAX) ;

    -- Success

    SELECT @EmpIDVariable = COALESCE(@EmpIDVariable + ', ', '') + CAST(BusinessEntityID AS NVARCHAR(MAX))

    FROM HumanResources.Employee

    ORDER BY BusinessEntityID DESC ;

    SELECT @EmpIDVariable ;

    -- Failure

    SET @EmpIDVariable = NULL ;

    SELECT @EmpIDVariable = COALESCE(@EmpIDVariable + ', ', '') + CAST(BusinessEntityID AS NVARCHAR(MAX))

    FROM HumanResources.Employee

    ORDER BY CASE WHEN NationalIDNumber > 100000000 THEN 0 ELSE 1 END ASC ;

    -- OR

    -- ORDER BY 1 ASC ;

    SELECT @EmpIDVariable ;

    Imho a problem of the optimizer. Seems that ORDER BY works on columns in the table, but not on expressions. So it's in the end only a cursor or FOR XML.

  • stefan.hoffmann (3/21/2011)


    hi Hugo,

    http://msdn.microsoft.com/en-us/library/ms187953.aspx

    states that the result is defined as the value of the last row and not undefined as Umachandar in the Connect ticket says, should be corrected then.

    That page is about the syntax SELECT @variable = column, not about the syntax SELECT @variable = @variable + column. The latter is undocumented. And the results are, as Umachandar says, undefined.

    Imho a problem of the optimizer. Seems that ORDER BY works on columns in the table, but not on expressions. So it's in the end only a cursor or FOR XML.

    Definitely a result of how the optimizer creates plans, but I would not call it a problem. Since the behaviour of this specific syntax is not defined, both results can be considered correct. (In fact, I tend to find the result with only one value in the list a bit more correct than the result with all the rows, since set-based SQL is supposed to behave as if all rows are processed at once, not one by one).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • hi Hugo,

    I'd like to disagree. While the last example uses only @var = column the explanation above it clearly states:

    "If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:"

    "references a nonscalar expression" should describe exactly our SELECT @var = @var + column FROM table ORDER BY columnOrExpression scenario.

    Or is there any subtle interpretation I don't get here, maybe my English is not sufficient enough...

  • I believe that the method in the original article is documented as unsupported by Microsoft, and is subject to change, and it may not work in future releases of SQL Server.

    But, the technique has been known for years, and it's hard to see how Microsoft can actually remove this behavior now. You never know.

  • Since the example doesn't do any concatenation I wouldn't assume that it's referring to the situation that the article is discussing.

    USE AdventureWorks2008R2;

    GO

    DECLARE @EmpIDVariable int;

    SELECT @EmpIDVariable = BusinessEntityID

    FROM HumanResources.Employee

    ORDER BY BusinessEntityID DESC;

    SELECT @EmpIDVariable;

    GO

    All the example is showing is that if you're pulling multiple rows what's returned last gets assigned to the variable. It doesn't state anything about how the expression is compiled so if it's more than just a simple column pull this doesn't document the behavior.

  • Woof...how it will?:unsure:

  • Doesn't the FOR XML method blow up when certain characters are in place in the data??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Here is how I grab a Project record and all Sites associated with it using the STUFF and FOR XML

    SELECT p.ProjectDescription,

    STUFF((SELECT ',' + s.SiteCode FROM ProjectSitesps

    JOIN

    ALL_SitessON ps.SiteID = s.SiteID

    WHERE ps.ProjectNum = @ProjectNum ORDER BY s.SiteCode FOR XML PATH ('')

    )

    ,1,1, '') As ProjectSites

    FROM Projects p

    WHERE p.ProjectNum = @ProjectNum

    I got htis from Sharath_123 on EE

  • TheSQLGuru (3/21/2011)


    Doesn't the FOR XML method blow up when certain characters are in place in the data?

    Unless there's a character other than the three big ones I know that could cause it to have issues it works fine. The output may just need a little more massaging. The first query below shows the direct method. The second one puts the real value back in.

    declare @xml_char table (

    xmlchar char(1)

    )

    insert into @xml_char

    select '<'

    union all

    select '>'

    union all

    select '&'

    SELECT REPLACE((SELECT DISTINCT RTRIM(LTRIM(xmlchar)) AS 'data()'

    FROM @xml_char

    FOR XML PATH ( '' )), ' ', ', ')

    SELECT REPLACE(REPLACE(REPLACE(REPLACE((SELECT DISTINCT RTRIM(LTRIM(xmlchar)) AS 'data()'

    FROM @xml_char

    FOR XML PATH ( '' )), ' ', ', '),'&','&'),'>','>'),'<','<')

Viewing 15 posts - 31 through 45 (of 87 total)

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