SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

  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


-- 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 --------------------
  id, email, name
FROM EmailList
WHERE emailsent = 0

OPEN eMailCursor

FETCH NEXT FROM eMailCursor INTO @Id, @email, @name

  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

CLOSE eMailCursor
------------------ CURSOR eMail (END) --------------------


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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.