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.
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:
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.
The following example codes demonstrate how to use EASendMail SMTP component to send multiple emails with cursor.
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 = 'email@example.com' 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 = 'firstname.lastname@example.org' 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
At next section I will introduce how to send mass emails with EASendMail Service Queue in SQL stored procedure.
If you have any comments or questions about above example codes, please click here to add your comments.