CODEWITHSUNDEEP
sqlsql-serversql-server-2012
yogi
yogi

Reputation: 19591

Weird behaviour from SQL query

I wrote a query (this is the simplest form of that query)

declare @tbl1 table(name varchar(50))
declare @tbl2 table(name varchar(50))
declare @query varchar(600)
set @query = ''

insert into @tbl1
select 'a' union select 'b'  union select 'c'  union select 'd'  union select 'e'

insert into @tbl2
select '1' union select '2'  union select '3'  union select '4'  union select '5'

select 
 @query = 
 ' Go ' + 
 @query +
 tbl1.name + ' (' +
 substring(
 (
    select ', ' + tbl2.name
    from @tbl2 as tbl2
    for xml path('')
 ), 3, 5000) + ') '
from @tbl1 as tbl1

print @query

I was expecting a result like this

 GO a (1, 2, 3, 4, 5) GO  b (1, 2, 3, 4, 5)  GO c (1, 2, 3, 4, 5)  GO d (1, 2, 3, 4, 5)  GO e (1, 2, 3, 4, 5)

But this mean query return this to me

 Go  Go  Go  Go  Go a (1, 2, 3, 4, 5) b (1, 2, 3, 4, 5) c (1, 2, 3, 4, 5) d (1, 2, 3, 4, 5) e (1, 2, 3, 4, 5)

Can somebody please explain this result to me I fail to understand this.

Upvotes: 3

Views: 118

Answers (2)

Guffa
Guffa

Reputation: 700192

If you disregard the details in the query part of the string, you are doing this:

@query = ' Go ' + @query + 't(...) '

as @query is empty from start, the first iterations is:

@query = ' Go ' + '' + 't(...) '

which is ' Go t(...) '. Next iteration becomes:

@query = ' Go ' + ' Go t(...) ' + 't(...)'

which ends up as ' Go Go t(...) t(...) '.

As you see, the Go is added first in the string, and the query last, so you end up with all Go in the beginning of the string.

Upvotes: 3

Aditi
Aditi

Reputation: 1188

You need to change it to

select 
 @query = 
 @query +
 ' Go ' +    
 tbl1.name + ' (' +
 substring(
 (
    select ', ' + tbl2.name
    from @tbl2 as tbl2
    for xml path('')
 ), 3, 5000) + ') '
from @tbl1 as tbl1

Upvotes: 4

Related Questions