How to Create and Use a Sample SQL Cursor and T-SQL Cursor Example Code
Here is a SQL cursor example code created for looping through a list of
records as a result of a select query, which enables the sql developer to
execute a stored procedure for each row in the cursor which use the values
fetched by the cursor as the input arguments. The sample cursor is developed on
a MS SQL Server and is a sample for sql server cursor. The sql codes may use t-sql
codes so the sql cursor example may have differences than a typical pl/sql cursor or an
Oracle cursor.
The sample sql cursor codes below illustrates a process of merging duplicate
customer records kept in an application database. Assume that the duplicate
customers list and relation among the duclicate customer records are inserted
into and kept in a table named DuplicateCustomers which is simply formed of
columns MasterCustomerId, DuplicateCustomerId and some other columns like
MergeDate, IsMerged, MergedByUserId, InsertedDate, InsertedByUserId, etc which
are used during processing some details and useful in the reporting of the
duplicate record merge process results.
The list of the original customer records and the duplicate customer records
can be selected by the sql select query below:
SELECT MasterCustomerId, DuplicateCustomerId
FROM DuplicateCustomers WHERE IsMerged = 0
You can either create a temporary table to keep the result set in order to
use your initial set of records in the next steps of your process. Or you can
just use the above transact sql query to supply your records set to feed the t-sql cursor example we
will create.
Here with the variable declarations we will set column values we fetch with the tsql cursor to the variables.
DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int
Then the sql cursor definition or the t-sql cursor declaration code takes place.
DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DuplicateCustomerId
FROM DuplicateCustomers WHERE IsMerged = 0
During the example sql cursor declaration you can set the sql cursor properties or the
attributes of the cursor. Note that the sample cursor declaration uses the FAST_FORWARD
key attribute in order to create a sql cursor with a high performance. Since FAST_FORWARD
states that the cursor is FORWARD_ONLY and READ_ONLY the performance of the
cursor is optimized.
The t-sql syntax of cursor declaration command DECLARE CURSOR is stated as below in MSDN :
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
You can find more on how to declare a t-sql cursor and cursor attributes in Books Online
With the call of the OPEN command the t-sql server cursor is opened and the
cursor is populated with the data by the execution of the select query of the
DECLARE CURSOR command.
OPEN merge_cursor
So the OPEN command runs or executes the "SELECT MasterCustomerId,
DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0" select query
defined in the DECLARE CURSOR definition command which is set after FOR key.
With the execution of this select query the cursor is populated with the rows or
the data returned as a result set of the query.
The next step in using a cursor is fetching the rows from the populated
cursor one by one.
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
The syntax of the FETCH command is as follows
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
With the use of the NEXT, the FETCH NEXT command returns the next row following
the current row. If FETCH NEXT is called for the first time for a cursor, or we
can say if it is called after the OPEN CURSOR command, then the first row in the
returned result set is fetched or returned. The column values in the returned
row can be set into variables with the INTO key and by giving the names of the
variables as a comma seperated list after the INTO key.
So for our example the first row in the return result set of the cursor is set
into two variables named @MasterId and @DuplicateId. Here one important point is
the first column of the result set (column named MasterCustomerId) is set to
first variable defined in the list which is the @MasterId variable. And the
secod column named DuplicateCustomerId is set to the second variable @DuplicateId.
So the variable types must be carefully declared according to the column
types of the selected rows.
After the FETCH command, you should always control the value of the @@FETCH_STATUS.
This variable returns the status of the last cursor FETCH command in the current
connection.
The possible return values of @@FETCH_STATUS are;
| 0 |
FETCH statement was successful |
| -1 |
FETCH statement failed or the row was beyond the result set |
| -2 |
Row fetched is missing |
By always checking the @@FETCH_STATUS and controlling that it is value is
equal to "0" we will have a new row fetched. When the fetched status is
different than the "0" we can say that we have no more records are fetched.
In short, the value of @@FETCH_STATUS variable is the controlling parameter of
the loop we will use during processing all records or rows in the cursor.
In the body part of the WHILE statement the codes to process each row
returned by the cursor takes place. This code block changes according to your
reason to create and use a cursor. I placed an EXEC call for a sql stored
procedure and an UPDATE sql statement here in order to show as a sample.
The most important thing to care for the inside codes of the WHILE code block
is the last code statement FETCH NEXT command is recalled to get the next row
from the return cursor data set.
After all the records are processed the @@FETCH_STATUS parameter returns -1,
so the cursor can be now closed with the CLOSE CURSOR command. CLOSE CURSOR
releases the current result set. And the
DEALLOCATE CURSOR command releases the last cursor reference.
Here you can find the full sql cursor example code used in this article for
explaining the t-sql cursors in SQL Server.
DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int
SELECT @MergeDate = GetDate()
DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId,
DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DuplicateId
UPDATE DuplicateCustomers
SET
IsMerged = 1,
MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId AND
DuplicateCustomerId = @DuplicateId
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
Additional SQL Cursor Tutorials & T-SQL Cursor Examples
You can find more sql cursor tutorials and sql cursor example at the following articles :
SQL Cursor Example - List Count of Rows in All Tables in Database using SQL Server Cursor
T-SQL Cursor Example Code
|