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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems