Programming in MS SQL Server 2012

OUTPUT Cursor Data

Transact-SQL stored procedures can use the cursor data type only for OUTPUT parameters.

If the cursor data type is specified for a parameter, both the VARYING and OUTPUT keywords must be specified for that parameter in the procedure definition.

A parameter can be specified as only OUTPUT but if the VARYING keyword is specified in the parameter declaration, the data type must be cursor and the OUTPUT keyword must also be specified.

The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a procedure, procedures with cursor OUTPUT parameters cannot be called from the database APIs.

These procedures can be called from Transact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

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

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

-- Example 114 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'dbo.usp_EmployeeCursor' AND type = 'P')

DROP PROCEDURE dbo.usp_EmployeeCursor

CREATE PROCEDURE dbo.usp_EmployeeCursor

@EmployeeCursor CURSOR VARYING OUTPUT

SET NOCOUNT ON;

SET @EmployeeCursor = CURSOR

FORWARD_ONLY STATIC FOR

SELECT Emp_Code, Emp_Name FROM EMP;

OPEN @EmployeeCursor;

Query Output Screen

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.

-- Example 115 --

USE EBusiness

DECLARE @MyCursor CURSOR

EXEC dbo.usp_EmployeeCursor @EmployeeCursor = @MyCursor OUTPUT

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM @MyCursor;

END

CLOSE @MyCursor

DEALLOCATE @MyCursor;

Query Output Screen

* * * * *

Your Comment To AUTHOR