Build a string variable while looping through query results.

Question

question

jdasupport123 asked Oct 6, '15

Build a string variable while looping through query results.

I need to attach a file for each result in a query, could be a single result could be 10. Using Microsoft SQL server 2012. example: `select ItemNumber from table where column_x=22:` Results: >125487, >25645124 Declare @path nvarchar(255); Set @path = 'C:\Users\Public\Documents\' Declare @attachment nvarchar(255); Set @attachment = @path + result1 + '.txt' + ',' + @path + result2 + '.txt' I believe that I could be going about this totally wrong.

cursor sql 2012 while-loop sp_send_dbmail

1

10 |1200

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

Dave_Green ♦ commented · Oct 06, 2015 at 06:10 PM

One point from [MSDN]( https://msdn.microsoft.com/en-gb/library/ms190307.aspx) is that the list should be semi-colon delimited, not comma delimited - e.g @path + result1 + '.txt' + ';' + @path + result2 + '.txt'

0 ·

Answer 1 · 2015-10-06T18:56:41Z

Tom Staab answered Oct 6, '15

DECLARE @path nvarchar(255); SET @path = 'C:\Users\Public\Documents\'; DECLARE @attachment varchar(max) = ''; SELECT @attachment += CONCAT(';', @path, ItemNumber, '.txt') FROM MyTable WHERE column_x=22 ; SET @attachment = STUFF(@attachment, 1, 1, ''); -- remove initial ";"

1

10 |1200

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

jdasupport123 commented · Oct 06, 2015 at 08:24 PM

Excellent, thank you very much!!

0 ·

question