如何从查询结果构建Sp_send_dbmail附件字符串

Question

我需要为查询中的每个结果附加一个文件，可以是单个结果，可以是10。使用Microsoft SQL server 2012.

示例:select ItemNumber from table where column_x=22:

结果:

125487年

,

25645124

Declare @path nvarchar(255);
Set @path = 'C:UsersPublicDocuments'
Declare @attachment nvarchar(255);
  Set @attachment = @path + result1 + '.txt' + ',' + @path + result2 + '.txt'

Answer 1

CREATE Procedure [dbo].[GetItems]
@TransNumber INT,
 @attachment nvarchar(MAX)  OUTPUT
AS 
 Begin
 DECLARE @path nvarchar(255);
 SET @path = 'C:UsersPublicDocumentsWineCards';
 set @attachment = ''

 SELECT @attachment += CONCAT(';', @path, 'wn', ItemLookupcode, '.pdf')
 FROM TransactionEntry
 Left Join Item on TransactionEntry.ItemID = Item.ID
 WHERE TransactionEntry.TransactionNumber=@TransNumber and         dbo.fn_FileExists(CONCAT(@path, 'wn', ItemLookupcode, '.pdf') )=1
 Group by Itemlookupcode

;

select @attachment = STUFF(@attachment, 1,1， ");——去掉首字母";"

结束