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.

Sections:

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

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:

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.

Tutorial Index

Send Multiple Emails with Cursor in SQL Server Stored Procedure¶

Introduction¶

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

Share

See Also