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    PaymentDate
1        [email protected]     John Smith    $1244.34   01/01/06
2        [email protected]   James April   $456.10    01/15/06
3        [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   PaymentDate
1         1      $10       12/01/05
2         1      $12       11/01/05
3         2      $34       12/12/05
4         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 @[email protected], @[email protected], 
  @subject='Payment Notification',@[email protected]

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.34
Please pay your balance before 01/1/2006
For your convenience we attach last 10 transactions below:

PaymentDate                Payment
-------------------        ---------
2005-12-01 00:00:00        $10.0000
2005-11-01 00:00:00        $12.0000 

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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.