Working with sub-folders in file path with sp_send_dbmail and T-SQL

Question

Is it possible to populate a sub-folder name in the @file_attachements path with some use of a wildcard?

I run a query to get the document name I'm looking for. I then assign that to a variable and concatenate the @file_location with @document_name to get the full path, but unfortunately I have an issue where that file name would be located in a sub-folder within the @file_location.

I have the following setup as an example:

DECLARE @file_attachment VARCHAR(MAX)
DECLARE @file_location VARCHAR(MAX)
DECLARE @my_email VARCHAR(MAX)
DECLARE @BodyContent VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
DECLARE @DocumentName VARCHAR(MAX)

SET @DocumentName = (SELECT Cast(Document_Name AS VARCHAR(MAX)) as 'DocumentName'
                     FROM   dbo.tblDocuments
                     WHERE  TemplateID = 471
                            AND Cast(Document_Date AS DATE) = Cast(Getdate() - 1 AS DATE))
SET @file_location ='\\server\directory\sub-directory\'
SET @my_email = '[email protected]'
SET @file_attachment = @file_location + @DocumentName  

The actual file would be located here \\server\directory\sub-directory\another sub-dir\myfile.docx

Since the second sub-directory will be different. I can't seem to account for that when I concatenate the @file_location and @DocumentName.

I can get it to send fine if I hard code the second sub-directory in the @file_location, but as I mentioned, that second folder will change.

The @file_attachments is then set to = @file_location when setting up sp_send_dbmail.

Answer 1

I would use xp_dirtree for this since it will find files in sub-folders too if we set the depth to 0.

DECLARE @ParentFolder VARCHAR(256) = '\\server\directory\sub-directory\'

IF OBJECT_ID('tempdb..#FileNames') IS NOT NULL DROP TABLE #FileNames
CREATE TABLE #FileNames (
    id int IDENTITY(1,1)
    ,subdirectory nvarchar(512)
    ,depth int
    ,isfile bit)
INSERT #FileNames (subdirectory,depth,isfile)
EXEC xp_dirtree @ParentFolder , 0, 1

SELECT * FROM #FileNames