How Can I Send Dynamic Emails from SQL Server Using a Recipient List Table?

Sending Email from SQL Server with Dynamic Recipient List

Sending email from SQL Server can be a powerful tool for streamlining communication, but it can become more complex when the recipient addresses are stored in a table. Here's how you can tackle this challenge in T-SQL:

Step 1: Configure Profile and Account

Configure a Database Mail profile and account using the Database Mail Wizard. This will allow you to specify SMTP settings and credentials.

Step 2: Enable Extended Features

Execute the following commands in sequence to enable advanced features and Database Mail XPs:

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

Step 3: Loop Through Table and Send Emails

To send emails to recipients listed in a table, use a cursor or WHILE loop to iterate through the rows:

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]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

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

END

This code will loop through the [email_adresses] table, retrieve the email address, and send an email using the specified profile and content.

The above is the detailed content of How Can I Send Dynamic Emails from SQL Server Using a Recipient List Table?. For more information, please follow other related articles on the PHP Chinese website!