Using Transact-SQL Cursor Syntax
In order to using Transact-SQL cursors, you have to perform the following steps:
-
Create a cursor with declare cursor statement based on a SELECT statement.
Declare cursor statement :
DECLARE cursor_name CURSOR FOR select_statement
- Populate the cursor with Open statement
The Open cursor statement
OPEN {{[GLOBAL] cursor_name} cursor_variable_name} - Use the fetch statement to change the current record in the cursor and stores that record into a local variables
The FETCH cursor statement
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @NVAR} | RELATIVE {n | @NVAR} ]FROM] {{[GLOBAL] cursor_name }|@cursor_variable_name} [INTO @variable_name[, . . .n] ] - Process the retrieved data
- Loop the step 3 and 4 until the final record in the result set is reached
- Close the cursor with close statement
CLOSE {{[GLOBAL] cursor_name }|cursor_variable_name} - Deallocate the cursor by using Deallocate statement
DEALLOCATE {{[GLOBAL] cursor_name }|@cursor_variable_name}
It would be easier to explain how to use transact-SQL cursor work via an example. Let's move to the an example how to use cursor.