Posted by Plamen Ratchev on 01/09/07 03:24

Erland,

I believe you are referring to the behavior explained in this KB article:
http://support.microsoft.com/default.aspx/kb/287515

The way this query is written it follows exactly the workaround solution
explained in the article (to apply any function or expression to the SELECT
list columns rather than in the ORDER BY clause). According to the article
then the query will achieve the expected results.

This query was tested with both SQL Server 2000 and SQL Server 2005. I agree
it does not exactly comply with the ANSI specifications but seems SQL Server
handles that well.

Please correct me if I am missing the point.

I did look at the recent thread after you pointed out and the solution you
provided is in fact more standard and avoids any speculation about
unexpected behavior.

Regards,

Plamen Ratchev
http://www.SQLStudio.com


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98B25FC487BAYazorman@127.0.0.1...
> Plamen Ratchev (Plamen@SQLStudio.com) writes:
>> Here is one way to accomplish what you need using a function. Be aware of
>> the limitation of 8000 characters for varchar. If you are using SQL
>> Server
>> 2005 you can use varchar(max) to expand beyond the 8000 character limit.
>>...
>> SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + col2 FROM Test
>> WHERE col1 = @col1 ORDER BY col2
>
>
> Also beware that this solution relies on undefined behaviour. This may
> work - or it may not.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]