T-SQL Cursor Example Code
Here is a T-SQL cursor example code created for looping selecting a list of email addresses for emailing.
The select query in the definition of the example t-sql cursor returns the example record set that will be used for emailing purposes.
After we declare and open the example sql cursor, by fetch next method in the sample cursor source rows, we will loop and send email in each loop with in the record set.
To send email to an email address from MS SQL Server, we will use sp_send_dbmail SQL Server mailing enhancement by defining a email profile and setting SMTP properties for the related SQL Server DBMail profile.
Let's first create a sql database table which will hold the sample email data for our t-sql cursor example.
GO
CREATE TABLE EmailList (
id smallint identity(1,1),
email varchar(500),
name nvarchar(500),
emailsent bit default 0,
sentdate datetime
)
Now we are ready to populate our sql email table with sample data.
In our sql cursor example we will loop for each row in this table and send email to email addresses that are not sent email before.
Within t-sql code in our t-sql cursor example, we will update rows that are sent email.
INSERT INTO EmailList (email, name) VALUES ('emailaddress1@test.com', N'Darth Vader')
INSERT INTO EmailList (email, name) VALUES ('emailaddress2@test.com', N'Bill Gates')
INSERT INTO EmailList (email, name) VALUES ('emailaddress3@test.com', N'Katy Perry')
And now let's code t-sql script for our example t-sql cursor.
CREATE PROC SendEmailCursor
AS
-- eMail Variables --
DECLARE @email_subject nvarchar(1000)
DECLARE @email_body nvarchar(max)
SET @email_body = N'Welcome to our Community'
SET @email_body = N'<html><body>Dear {0},<br />We''re glad to see you.</body></html>'
-- eMail Variables (END) --
-- Cursor Variables --
DECLARE @Id smallint;
DECLARE @email varchar(500);
DECLARE @name nvarchar(500);
-- Cursor Variables (END) --
DECLARE @pbody nvarchar(max)
------------------ CURSOR eMail --------------------
DECLARE eMailCursor CURSOR FAST_FORWARD FOR
SELECT
id, email, name
FROM EmailList
WHERE emailsent = 0
OPEN eMailCursor
FETCH NEXT FROM eMailCursor INTO @Id, @email, @name
WHILE @@FETCH_STATUS = 0
BEGIN
---
SET @pbody = REPLACE(@email_body, '{0}', @name)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'TBS',
@recipients = @email,
@subject = @email_subject,
@body = @pbody,
@body_format = 'HTML'
UPDATE EmailList SET emailsent = 1, sentdate = GetDate() WHERE id = @Id
---
FETCH NEXT FROM eMailCursor INTO @Id, @email, @name
END
CLOSE eMailCursor
DEALLOCATE eMailCursor
------------------ CURSOR eMail (END) --------------------
GO
Now we can end our cursor example tutorial by calling the stored procedure we have code the sql cursor in.
If the DBMail profile settings are correct, you will soon get emails sent through the sql cursor example we have declared and execured above.
EXEC SendEmailCursor
Additional SQL Cursor Tutorials and T-SQL Cursor Examples
You can find more sql cursor tutorials and sql cursor example at the following articles :
How to Create and Use a Sample SQL Cursor and T-SQL Cursor Code
SQL Cursor Example - List Count of Rows in All Tables in Database using SQL Server Cursor