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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

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

           DROP PROCEDURE dbo.usp_EmployeeCursor

GO

 

CREATE PROCEDURE dbo.usp_EmployeeCursor

          @EmployeeCursor CURSOR VARYING OUTPUT

AS

          SET NOCOUNT ON;

          SET @EmployeeCursor = CURSOR

               FORWARD_ONLY STATIC FOR

          SELECT Emp_Code, Emp_Name FROM EMP;

          OPEN @EmployeeCursor;

GO


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

GO

 

DECLARE @MyCursor CURSOR

 

EXEC dbo.usp_EmployeeCursor @EmployeeCursor = @MyCursor OUTPUT

WHILE (@@FETCH_STATUS = 0)

BEGIN

          FETCH NEXT FROM @MyCursor;

END

CLOSE @MyCursor

DEALLOCATE @MyCursor;

GO


Query Output Screen



* * * * *


Email Your Comment To AUTHOR