SQL Server: Recursive dynamic SQL puzzle I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as explained below.


Create a new database
Create a new table with three columns as in below script.(insert
values not required)

Create table table1(id int, name varchar(20), city varchar(20))


Execute below T-SQL Script, which results xxx [id] [name] [city]  in single row.

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
SELECT @a



Where as below script results in 3 rows

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT 'b' = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 


To understand more better I executed the below query using Top 1. This results xxx [id]  

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT Top 1 @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
SELECT @a


I got puzzled With Top 2 query. This resulted xxx [id] [name]. 

Where as I was expecting xxx [id] xxx [id] [name] i.e. First value + second value. Where am I going wrong.

I am curious to know how the column names getting concatenated. Sorry I could not frame better title for this puzzle.
I am curious to know how the column names getting concatenated.


Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL) 


  Do not use a variable in a SELECT statement to concatenate values
  (that is, to compute aggregate values). Unexpected query results may
  occur. This is because all expressions in the SELECT list (including
  assignments) are not guaranteed to be executed exactly once for each
  output row.


Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');


Result: xxx [city][id][name]