Complete Guide to Batch Email Sending in SQL Server Using T-SQL

Database Mail Configuration Fundamentals

Before sending emails using T-SQL in SQL Server, the database mail system must be properly configured. Database Mail is SQL Server's built-in feature that enables email sending through SMTP servers. The configuration process involves creating mail profiles and associated accounts, which define sender server parameters, authentication details, and other mail settings.

Key configuration steps include: using the "Configure Database Mail" wizard in SQL Server Management Studio to create profiles, setting SMTP server parameters, and ensuring database mail functionality is enabled. The following T-SQL commands enable database mail extensions:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

These commands first display advanced configuration options, then enable the extended stored procedures related to database mail. After execution, the SQL Server instance can process email sending requests.

Basic Email Sending Operations

The sp_send_dbmail stored procedure is the core tool for sending emails in SQL Server, located in the msdb system database. Basic usage involves specifying parameters such as recipients, subject, and message body. Here's a simple sending example:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Database Mail Received Successfully.'

Key parameter explanations: @profile_name specifies the mail profile to use, @recipients defines recipient addresses (supports multiple addresses separated by semicolons), @subject sets the email subject, and @body contains the message content. When mail is successfully queued, the stored procedure returns the mail item ID.

Looping Through Table-Based Email Addresses

In practical applications, email addresses are typically stored in database tables, requiring iteration through all records and individual sending. The following example demonstrates how to use loop structures to process all email addresses in the email_adresses table:

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses] WHERE id=@id

    SET @query='EXEC msdb.dbo.sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Database Mail Received Successfully.'''

    EXEC (@query)
    SELECT @id=MIN(id) FROM [email_adresses] WHERE id>@id
END

This code first declares necessary variables, then determines the loop range using MIN and MAX functions. The WHILE loop iterates through each ID, dynamically constructing and executing sp_send_dbmail commands. This approach ensures each email address receives independent emails while maintaining code clarity and maintainability.

Advanced Features and Parameter Details

sp_send_dbmail supports various advanced features, including CC and BCC recipients, message importance settings, file attachments, and query result integration. Here are detailed explanations of some important parameters:

The @copy_recipients and @blind_copy_recipients parameters allow specifying CC and BCC recipient lists, also supporting multiple addresses separated by semicolons. The @importance parameter controls email priority with optional values Low, Normal, or High, affecting how emails are displayed in clients.

File attachment functionality is implemented through the @file_attachments parameter, which accepts semicolon-separated lists of absolute file paths. However, note that this feature is unavailable in Azure SQL Managed Instance as it cannot access the local file system.

Query integration is one of sp_send_dbmail's powerful features. The @query parameter allows executing arbitrary T-SQL queries, with results sent as either email body or attachments. Related parameters include:

• @attach_query_result_as_file: Controls whether query results are sent as attachments instead of body content
• @query_attachment_filename: Specifies attachment file names
• @query_result_header: Controls whether column headers are included
• @execute_query_database: Sets the database context for query execution

HTML Formatted Email Sending

Beyond plain text emails, sp_send_dbmail also supports HTML-formatted email content. By setting the @body_format parameter to 'HTML', richly formatted emails can be sent. The following example demonstrates how to construct HTML emails containing tables:

DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML = N'<H1>Work Order Report</H1>' + N'<table border="1">'
+ N'<tr><th>Work Order ID</th><th>Product ID</th>'
+ N'<th>Name</th><th>Order Qty</th><th>Due Date</th>'
+ N'<th>Expected Revenue</th></tr>'
+ CAST((SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder AS wo
INNER JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2022-04-30'
AND DATEDIFF(dd, '2022-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))
+ N'</table>'

EXECUTE msdb.dbo.sp_send_dbmail
@recipients = 'yourfriend@adventure-works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML'

This code uses FOR XML PATH to construct HTML tables, converting query results into formatted HTML content. This approach is particularly suitable for sending report-type emails containing data.

Error Handling and Best Practices

In production deployments, robust error handling is crucial. sp_send_dbmail provides multiple error handling mechanisms:

The @append_query_error parameter controls behavior during query errors. When set to 1, emails are sent even if queries fail, with error information included in the body; when set to 0, query failures cause entire email sending to fail.

Permission management is another critical consideration. Executing sp_send_dbmail requires users to be members of the DatabaseMailUserRole in the msdb database. Additionally, users must have permission to use the specified mail profiles.

Performance optimization recommendations include: avoiding frequent sp_send_dbmail calls in loops, considering batch processing; for large email volumes, monitoring database mail queue status; properly setting query timeouts and attachment size limits.

Security considerations: Ensure appropriate security settings for mail profiles, avoid hardcoding sensitive information in code, regularly review email sending logs to detect anomalous activities.