SQL : Loop through folder and Send email with attachment and archive file.Exec sp_send_dbmail

Question

Hi Everyone – I am trying to create the below Stored Procedure where the patient should receive the attachment document via email.

The documents are in the folder and each document filename (Example: LetterPatient_12345) has a patient ID (12345) appended to it. How do I create the Stored Procedure to look for patient ID in the folder. Below is the folder where all the documents exist by patient ID.

W:\Files\Shared\Letters\Test

LetterPatient_12345

LetterPatient_56789

LetterPatient_10112

Here is the complete Stored Procedure with the above query plugged in:

Declare @From nvarchar(max) = 'Dev <Development@un.org>'

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

Do all files follow that naming convention of LetterPatient_PatientId? — Tim Mylott, Oct 10 '18 at 14:14
@TimMylott: No, File naming convention will change. But the file will have the patientid at the end of the filename and all these are pdf examples of filenames: LetterPatient_patientid MailAssistance_1_patientid PremiumAssistance_123_PatientID — user9273914, Oct 10 '18 at 14:24
Why use a stored procedure? Why not just use a simple script (python?) in combination with a SQL query (if you would like to fetch data from database) .. It would also help to know when this should be triggered. — Saravana Kannadasan, Oct 10 '18 at 15:00

score 1 · Accepted Answer · answered Oct 10 '18 at 15:09

First, you need to tweak your cursor and add a @PatientId variable.

FETCH NEXT FROM Email_cursor INTO @RecipientsList,@FirstName,@LastName,@PatientId

Using this as reference: How to list files inside a folder with SQL Server

In using the xp_DirTree stored procedure, here's an example of how you could accomplish what you're after:

DECLARE @FilePath NVARCHAR(500);
DECLARE @FileAttachment NVARCHAR(MAX) = ''
DECLARE @PatientID INT

SET @PatientID = 20181002

SET @FilePath = N'W:\Files\Shared\Letters\Test';

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

Just a side note on the directory on where the files are located. The account that is running the SQL Server Service must have access to the directory where the files are located or SQL Agent or if running via a proxy account. I assume "W:\" is locate on the server where this stored procedure is getting executed. If not, it needs be accessible from the server and by the account.

SQL : Loop through folder and Send email with attachment and archive file.Exec sp_send_dbmail

1 Answers1