SQL Server: send mails based on a table.

The following code sample shows how to send a mail to clients based on a client table. This sample is using a loop.

You should avoid using a loop in your scripts. Try first to execute your task using an SQL statement. Even if you want to execute a delete based on a select statement, there is a way, using a sub-query. But in some cases, there is no other ways: for example sending a mail to each recipient stored in a table.

Here is the commented code sample sending mails in a loop:

@last_client_id bigint,
@id bigint,
@first_name varchar(30),
@email varchar(30),
@email_body varchar(30)
-- set the number of rows
-- returned to 1
-- in order to work on the
-- table row by row
set rowcount 1
-- @last_client_id will
-- hold the last client
-- id select. This is
-- in order to work on the
-- next ID at each iteration
-- On the first iteration,
-- we initialise this variable
-- to 0.
select @last_client_id = 0
-- We label the line below
-- with the marker START:
-- so as to create a loop
-- by returning to this line
-- at each iteration
-- select the next line
-- in the table and
-- populate the parameter
-- values with values
-- selected from this line
-- note that we select only
-- one row as we used
-- set rowcount 1
-- note that we populate
-- @last_client_id with the
-- current client id.
-- at the next iteration,
-- it will select a client
-- ID strictly greater than
-- this ID.
@last_client_id = ID,
@first_name = first_name,
@email = email
from #premium_clients
-- by using ID
-- > @last_client_id, we make
-- sure we always work
-- on a different line at each
-- iteration
ID > @last_client_id
-- If we found a next client ID in
-- the table
if @@ROWCOUNT = 1
-- in this example
-- we just say hello
-- to the client
select @email_body =
'Hello ' + @first_name
-- send the mail
exec msdb.dbo.sp_send_dbmail
@profile_name = 'MailTest',
@recipients = @email,
@subject = 'Mail Test',
@body = @email_body,
@body_format = 'text'
-- We go back to the
-- start of the loop
goto START
Share on:

Last udpated: September 10, 2017
Created: May 10, 2015