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 Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014

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

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;

0FETCH statement was successful
-1FETCH statement failed or the row was beyond the result set
-2Row 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 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






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems