question

ajit.ghule avatar image
ajit.ghule asked

SQL query help

**Can anybody please let me know why below query returns only B instead of AB ?** Declare @T1 table(C1 varchar(10) ) INSERT @T1 VALUES('A') INSERT @T1 VALUES('B') DECLARE @Str VARCHAR(MAX)='' SELECT @Str = @Str + C1 FROM @T1 ORDER BY LTRIM(RTRIM(C1)) Select @Str **Please let me know. Thanks in advance.**
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
That select doesn't 'loop around' updating the value of @Str as it goes, it's essentially running these 2 queries: @Str is initiallized to '' select @Str = '' + A select @Str = '' + B so the answer you get is the result of the last one To get what you want try select @Str = coalesce(@Str, C1) + C1 from @T1
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Also wanted to add, this was raised as a bug on Connect, but MS confirmed that this is by design, and should not be relied upon : http://connect.microsoft.com/SQLServer/feedback/details/606786/concatenation-into-a-scalar-variable-using-linked-server-in-from-clause-fails-when-casting-or-converting-data > ... we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution ...
2 Likes 2 ·
@ajit.ghule if @Kev Riley's answer helped you out, please vote it up by clicking on the thumbs up next to it. If you feel that it gave you the definitive answer to your question, please click on the check box next to the answer.
1 Like 1 ·
Thanks for your quick reply. But that select does 'loop around' updating the value of @Str when I remove order by. Can you please let me know why this is happening ?
0 Likes 0 ·
You'll also get 'AB' if you remove the LTRIM RTRIM : SELECT @Str = @Str + C1 FROM @T1 ORDER BY C1 This is due to the way the internal processing of the query is performed. If the original data set was large enough, the order of the resulting string would be unpredictable. It just so happens that 'A' and 'B' come out in the right order A related article can be found here : http://support.microsoft.com/kb/287515/en-gb
0 Likes 0 ·
This is a great help. Thank you very much Kev.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.