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 duplicate 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
