T-SQL Cursor
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.
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
)
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.
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')
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
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.
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
Cursor
Types
T-SQL cursors are generally bad
approach and are often misused. In today’s world of correlated subqueries,
CTE’s, recursive CTE’s, ranking and windowing functions (ROW_NUMBER, RANK,
DENSE_RANK, NTILE) and other tools, you have really few moments where cursor is
better solution. On the other hand, there are still scenarios where cursor are
best and most efficient solution.
If you think that holy moment of
cursor occurred, it’s good to know which type of cursor you should use. It’s
good to know what cursor features you need and choose a proper type of cursor.
Or you’re most probably wasting server resources. I tried to summarize
important aspects which you need consider when using cursors to prevent such
situation.
Here is important settings from
standard cursor declaration syntax:
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
Here is fast human description of
cursor options:
[ LOCAL | GLOBAL ] – Use LOCAL, cursor will live only in scope of batch.
GLOBAL cursor lives in whole connection, I can’t imagine many scenarios where
you really need it.
[ FORWARD_ONLY | SCROLL ] – Use FORWARD_ONLY, cursor will enable only sequential
forward only reading. SCROLL cursor enables to move freely forward and backward
which require more resources.
[ STATIC | KEYSET | DYNAMIC |
FAST_FORWARD ] – Use FAST_FORWARD, it is the
combination of READ_ONLY and FORWARD_ONLY cursor. FAST_FORWARD cursor
enables only forward and read only movement which makes it most efficient
compared to the other types. STATIC cursor loops over copy of data, not
over original data hence doesn’t support any updates. Creating copy of data is
very resource-intensive. KEYSET cursor requires unique key identifying rows,
these keys are copied in tempdb when cursor is opened. It loops then based on
those keys. You must have very good reason to use KEYSET cursor. DYNAMIC cursor
immediately reflects any changes made to underlying tables.
[ READ_ONLY | SCROLL_LOCKS |
OPTIMISTIC ] -READ_ONLY is good but doesn’t
support any updates. SCROLL_LOCKS option tells cursor to lock every read row to
ensure that updates through cursor will succeed.
And few more important points :
- start with LOCAL FAST_FORWARD cursor and then change it
if you need something special
- use minimal count of rows and columns within cursor
- think about what you’re doing inside cursor, try to
avoid complex SELECT queries inside
- try to avoid STATIC and KEYSET cursors, they work with
tempdb which is another overhead
- don’t forget to CLOSE and DEALLOCATE cursor regardless
of its scope settings
No comments:
Post a Comment