login

Subscribe to sql tutorial websiteRSS Feed
 » Database Cursor » Using Transact-SQL Cursor Syntax

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_name CURSOR 
   FOR select_statement
  • Populate the cursor with Open 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 local variables
	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.

Search