Cursors - MarkMpn/Sql4Cds GitHub Wiki

Cursors can be used to operate on individual rows one at a time.

A cursor must first be DECLAREd with the SELECT statement that defines the data that it will operate on. SQL 4 CDS currently only supports static cursors - any attempts to use dynamic, keyset or fast-forward cursors will generate an error.

Once declared, the cursor must then be OPENed ready for use. At this point the SELECT statement will be executed and a copy of the results stored in the cursor.

Data can now be FETCHed from the cursor. SQL 4 CDS implements all the available FETCH options, allowing you to navigate forwards and backwards through the data in the cursor.

The @@FETCH_STATUS function can be used to identify whether data was successfully FETCHed from the cursor - this is commonly used in a loop to process all the available data.

Once you have finished using the cursor it should be CLOSEd and DEALLOCATEd to release the resources used in SQL 4 CDS to store the data.

DECLARE

The following DECLARE syntax is supported:

ISO syntax:

DECLARE cursor_name [ INSENSITIVE ] CURSOR
    FOR select_statement
    [ FOR READ_ONLY ]

Transact-SQL extended syntax:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC ]
    [ READ_ONLY ]
    FOR select_statement

OPEN

The following OPEN syntax is supported:

OPEN [ GLOBAL ] cursor_name

FETCH

The following FETCH syntax is supported:

FETCH
    [ [ NEXT | PRIOR | FIRST | LAST
        | ABSOLUTE { n | @nvar }
        | RELATIVE { n | @nvar }
      ]
      FROM
    ]
[ GLOBAL ] cursor_name
[ INTO @variable_name [ ,...n ] ]

CLOSE

The following CLOSE syntax is supported:

CLOSE [ GLOBAL ] cursor_name

DEALLOCATE

The following DEALLOCATE syntax is supported:

DEALLOCATE [ GLOBAL ] cursor_name

Examples

-- Declare the cursor
DECLARE MyCursor CURSOR
FOR
SELECT name
FROM   account

-- Open the cursor - a snapshot of the data is taken at this point
OPEN MyCursor

-- Declare a variable to hold the data from each row
DECLARE @name NVARCHAR(100)

-- Try to get the first row from the cursor and store the results in @name
FETCH NEXT FROM MyCursor INTO @name

-- Keep iterating over the data in the cursor until we reach the end
WHILE @@FETCH_STATUS = 0
BEGIN
  -- TODO: Process data from current row, available in @name

  -- Move on to the next row
  FETCH NEXT FROM MyCursor INTO @name
END