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

Code

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

Code

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

Code

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

Code

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

Code

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

Code

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

Code

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

Code

Additional SQL Cursor Tutorials and 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
T-SQL Nested Cursor Sample in SQL Server 2008