Use CURSOR to Send Customized Email

Using SQL Mail and the xp_sendmail extended stored procedure, you can send email, assuming you’ve created a Microsoft Exchange Server profile or another Messaging API (MAPI) server profile. Let’s say you want to send a group of people in a table the same email message. One way to accomplish this task is to create a CURSOR, then fill the @recipients parameter in xp_sendmail. (If you’re unfamiliar with xp_sendmail’s syntax, see SQL Server Books Online—BOL.)

Although this approach is simple, it has a few disadvantages. Most spam filters block email messages if there are too many addresses in the recipient list. Plus, the email addresses of all the recipients are revealed. Perhaps more important, this approach is applicable only when you can send the same message to everyone on the list.

Companies often need to send mail messages that contain unique information for each person. For example, suppose you have the following Customers table:
 

CustID   Email               Name          Balance    PaymentDate1        [email protected]     John Smith    $1244.34   01/01/062        [email protected]   James April   $456.10    01/15/063        [email protected]     Sam Lee       $23.10     01/30/06 

You need to send email messages to the customers within 30 days of their payment date and let them know about their balance and payment date. Listing 1 shows the query you might use.

To make the stored procedure even more sophisticated, let’s attach a list of specific records for each customer by using xp_sendmail’s @query parameter. Let’s add the last 10 transactions from the Transactions table, which looks like

TransID   Cust   Payment   PaymentDate1         1      $10       12/01/052         1      $12       11/01/053         2      $34       12/12/054         2      $10       11/12/05

You need to add one more variable, @qry, to the loop:

set @qry='SELECT TOP 10 PaymentDate, Payment FROM Transactions   WHERE Cust=' + cast(@id as varchar(10)) + ' ORDER BY PaymentDate DESC'EXEC master.dbo.xp_sendmail @recipients=@email, @message=@msg,   @subject='Payment Notification',@query=@qry

Listing 2 shows the updated query. The query in Listing 2 will generate email messages that look like

Dear John Smith,This is information about your Account.Balance on the account: $1244.34Please pay your balance before 01/1/2006For your convenience we attach last 10 transactions below:PaymentDate                Payment-------------------        ---------2005-12-01 00:00:00        $10.00002005-11-01 00:00:00        $12.0000 

—Andrey Stanovnov

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.