question

jdasupport123 avatar image
jdasupport123 asked

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.
cursorsql 2012while-loopsp_send_dbmail
1 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

Excellent, thank you very much!!
0 Likes 0 ·

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.