SQL :循环访问文件夹并发送带有附件和存档文件的电子邮件.执行sp_send_dbmail

Question

大家好 – 我正在尝试创建以下存储过程，患者应通过电子邮件接收附件文档。

文档位于文件夹中，每个文档文件名(例如：LetterPatient_12345(都附加了一个患者 ID (12345(。如何创建存储过程以在文件夹中查找患者 ID。 下面是按患者 ID 存在所有文档的文件夹。

W：\文件\共享\字母\测试

LetterPatient_12345

LetterPatient_56789

LetterPatient_10112

下面是插入了上述查询的完整存储过程：

Declare @From nvarchar(max) = 'Dev <[e-mail]>'
DECLARE @Mail_Profile_Name VARCHAR(100)
SET @Mail_Profile_Name='DoNotReply'
DECLARE @MessageBody NVARCHAR(Max)
DECLARE @RecipientsList NVARCHAR(max) 
DECLARE @MailSubject NVARCHAR(500) = 'Reports'
DECLARE @EmailID INT
DECLARE @FirstName varchar(100),@LastName varchar(100)
DECLARE Email_cursor CURSOR FOR
Select distinct  PE.EmailAddress, lr.PatientFirstName, lr.PatientLastName, 
Lr.PatientId   from Letterrequest lr
Left join Patient PT on PT.PatientId = Lr.PatientId
Left join PatientEmail PE ON PE.PatientId = lr.PatientId
where 1=1
and PT.AssistanceCommunicationMethodId = 1
and PE.PreferredEmailIndicator = 1
and PE.InactiveDateTime IS NULL

OPEN Email_cursor 
FETCH NEXT FROM Email_cursor  INTO @RecipientsList,@FirstName,@LastName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @MessageBody = 'Dear ' + @FirstName + COALESCE(' ' + @LastName,'') + CHAR(13) + CHAR(10) + 'Please find the letter attached '

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Mail_Profile_Name,
@recipients = @RecipientsList,
@body = @MessageBody,
@subject = @MailSubject,
@Body_Format = 'HTML' ,
@from_address  = @From;


FETCH NEXT FROM Email_cursor  INTO @RecipientsList,@FirstName,@LastName
END
CLOSE Email_cursor
DEALLOCATE Email_cursor

Answer 1

首先，您需要调整光标并添加一个@PatientId变量。

从Email_cursor获取

下一个 @RecipientsList，@FirstName，@LastName，@PatientId

以此作为参考：如何使用 SQL Server 列出文件夹中的文件

在使用 xp_DirTree 存储过程时，下面是如何完成所需操作的示例：

DECLARE @FilePath NVARCHAR(500);
DECLARE @FileAttachment NVARCHAR(MAX) = ''
DECLARE @PatientID INT
SET @PatientID = 20181002
SET @FilePath = N'W:FilesSharedLettersTest';
DECLARE @FileList TABLE
(
[FileName] NVARCHAR(500)
, [depth] INT
, [file] INT
);
--using xp_DirTree:
--Parameters:
--directory - This is the directory you pass when you call the stored procedure; for example 'D:Backup'.
--depth  - This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
--isfile - This will either display files as well as each folder.  The default of 0 will not display any files.
--This gets a list of ALL files in your directory
--This before your cursor
INSERT INTO @FileList (
[FileName]
, [depth]
, [file]
)
EXEC [master].[sys].[xp_dirtree] @FilePath
, 1
, 1;

--Add this code inside your cursor to filter on only those files that contain the @PatientId and build out the string of files to attach.
SELECT @FileAttachment = @FileAttachment + @FilePath + '' + [FileName] + ';'
FROM   @FileList
WHERE PATINDEX('%' + CONVERT(NVARCHAR, @PatientID) + '%', [FileName]) <> 0
--This just removes the trailing ;
SET @FileAttachment = SUBSTRING(@FileAttachment,1,LEN(@FileAttachment)-1)
--Then you can add the @file_attachments parameter to sp_send_dbmail and set that equal to the @FileAttachment variable.
SELECT @FileAttachment

只是文件所在目录的旁注。运行 SQL Server 服务的帐户必须有权访问文件所在的目录或 SQL 代理，或者如果通过代理帐户运行。 我假设"W：\"位于执行此存储过程的服务器上。 如果没有，则需要从服务器和帐户访问它。