How to add attachment from a table in sql mail

To add an attachment from a table in SQL Mail, you can use the sp_send_dbmail stored procedure and specify the attachment using the @file_attachments parameter. Here's an example:

DECLARE @message_id int
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Your_Profile_Name',
    @recipients = '[email protected]',
    @subject = 'Test Email with Attachment',
    @body = 'This is a test email with an attachment.',
    @file_attachments = 'SELECT [File_Path] FROM [Your_Table_Name] WHERE [Condition]'

SELECT @message_id

In this example:

• @profile_name specifies the name of the SQL Mail profile that you want to use to send the email.
• @recipients specifies the email address of the recipient.
• @subject specifies the subject of the email.
• @body specifies the body of the email.
• @file_attachments specifies the attachment(s) to be sent with the email. In this case, we're selecting the File_Path column from a table Your_Table_Name where a condition is specified.

Make sure to replace Your_Profile_Name, [email protected], Your_Table_Name, and File_Path with your actual values.

Also, note that the @file_attachments parameter expects a list of file paths, so if you have multiple files to attach, you'll need to use a table-valued parameter or a string concatenation to build the list of file paths.

For example, if you have a table Attachments with columns ID, File_Name, and File_Path, you can use the following query to attach multiple files:

DECLARE @file_attachments nvarchar(max)
SELECT @file_attachments = STRING_AGG([File_Path], ';') FROM [Attachments]

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Your_Profile_Name',
    @recipients = '[email protected]',
    @subject = 'Test Email with Attachments',
    @body = 'This is a test email with multiple attachments.',
    @file_attachments = @file_attachments

In this example, the STRING_AGG function is used to concatenate the file paths into a single string, separated by semicolons. The resulting string is then passed to the @file_attachments parameter.