· A cursor is a mechanism you can use to fetch rows one at a time.
· Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
· When writing code for a transaction where the result set includes several rows of data, you may declare and use a cursor.
· For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
· Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
· Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
· Use the OPEN statement to execute the SELECT statement and populate the cursor.
· Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
· When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.
Disadvantages of cursors
· Each time a row is fetched from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the result set is.
· Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
· Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
