Posted by Erland Sommarskog on 05/11/06 15:35

Malc (M.B.Pinfold@massey.ac.nz) writes:
> Can somebody please tell me whether the following syntax is supported
> or whether it's a "feature" that will someday stop working. It works in
> both SQL Server 2000 and 2005 at the moment.
>
> declare @var varchar(1000)
> set @var = ''
> select @var = @var + colx from some_table where col1 = some_value

The correct result of this operation is undefined. You may get a
concatenated list, or only a single value.

I recommend against using this.

There is a supported way to do this on SQL 2005 with help of FOR XML PATH.
Unfortunately, I have the sample code at home only, so I cannot post it.



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

• Next in forum: Re: bug: data file autogrowth set to 12800%
• Prev in forum: Clustered Index considerations
• Thread view: Re: Select several values into a single variable

[Reply to this message]