Send Multiple Emails with Cursor in SQL Server Stored Procedure

In previous section, I introduced how to send email without specified SMTP server. In this section, I will introduce how to send multiple emails with cursor in SQL stored procedure.

Introduction

Most common email task in database is sending emails to a specified email list. To better understand the cursor, we need to create a simple table in your database like this:

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then insert a record in this table like this:

SQL table sample

Note

Remarks: All of samples in this section are based on first section: Send email in a simple SQL stored procedure. To run the following example codes successfully, please click here to learn how to create the test enivronment and use EASendMail in your project.

[SQL Stored Procedure Example - Send multiple emails with cursor]

The following example codes demonstrate how to use EASendMail SMTP component to send multiple emails with cursor.

Note

To get the full sample projects, please refer to Samples section.

DECLARE @email nvarchar(128)
DECLARE rcpt_cursor CURSOR FOR
SELECT email
FROM rcpts
OPEN rcpt_cursor
/* select all records from rcpts table and send it one by one */
FETCH NEXT FROM rcpt_cursor
INTO @email

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* If your smtp server requires SSL connection, please set @SSL = 1*/
DECLARE @SSL int
Set @SSL = 0

DECLARE @Port int
Set @Port = 25

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @email
    DECLARE @subject nvarchar(255)
    SELECT @Subject = 'test email for' + @email
    EXEC usp_SendTextEmail @ServerAddr,
            @from, @email, @subject, @BodyText, @User, @Password, @SSL, @Port
    FETCH NEXT FROM rcpt_cursor
    INTO @email
END
CLOSE rcpt_cursor
DEALLOCATE rcpt_cursor

Next Section

At next section I will introduce how to send mass emails with EASendMail Service Queue in SQL stored procedure.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.