What is a Cursor?
A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor, we need to perform some steps in the following order:
- Declare cursor
- Open cursor
- Fetch row from the cursor
- Process fetched row
- Close cursor
- Deallocate cursor
This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_IDFROM AccountsOPEN @getAccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDWHILE @@FETCH_STATUS = 0BEGIN
PRINT @AccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDEND
CLOSE @getAccountIDDEALLOCATE @getAccountID
Cursor ProcessThe process of creating, using and releasing cursors follow this pattern:DECLARE: associates the cursor with a record set and defines the cursor’s parametersOPEN: executes the SELECT statement to populate the cursorFETCH: retrieves a row from the cursorUPDATE/DELETE/etc.: manipulates/processes the dataCLOSE: closes the cursor, but leaves the underlying structure in place to be re-opened if neededDEALLOCATE: releases database and system resources and any locks on recordsCursor DeclarationsCursor declarations are complex statements with multiple arguments defining, not only the data contained in the cursor, but also how it can be accessed, if it can be changed, and how it will behave.DECLARE cursor_name CURSOR[LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | DYNAMIC | FAST_FORWARD | KEYSET][READ_ONLY | SCROLL_LOCKS | OPTIMISTIC][TYPE_WARNING]FOR SELECT parameters[FOR UPDATE [OF column_name [,…n]]]DECLAR cursor_name: creates and names the cursorCURSOR: dedicated data type