SQl Cursor

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 Process

The process of creating, using and releasing cursors follow this pattern:

DECLARE: associates the cursor with a record set and defines the cursor’s parameters

OPEN: executes the SELECT statement to populate the cursor

FETCH: retrieves a row from the cursor

UPDATE/DELETE/etc.: manipulates/processes the data

CLOSE: closes the cursor, but leaves the underlying structure in place to be re-opened if needed

DEALLOCATE: releases database and system resources and any locks on records

Cursor Declarations

Cursor 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 cursor

CURSOR: dedicated data type