Cursor Data Type in SQL Server

The CURSOR data type is used only for stored procedure output parameters and variables. It cannot be used within the CREATE TABLE statement. The CURSOR data type should be used to pass a reference to a resultset returned by a cursor. Variables declared by the CURSOR data type can be used with statements regularly used for cursors (DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE), as well as stored procedure output parameters and CURSOR_STATUS function. Some of the system procedures that describe resultsets within cursors could also be used with variables declared with CURSOR data type.

The following rules pertain to cursor output parameters when the procedure is executed:

1. For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

•    A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.
•   The procedure fetches the first 5 rows of result set RS.
•   The procedure returns to its caller.
•    The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

2. For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.

3. For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

4. For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

Example: 

In the following example, a stored procedure is created that specified an output parameter, @currency_cursor using the cursor data type. The stored procedure is then called in a batch

First, create the procedure that declares and then opens a cursor on the Currency table.

SQL

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

SQL

USE AdventureWorks2008R2;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO