Programming in MS SQL Server 2012 |
OUTPUT Cursor DataTransact-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 --
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 |
* * * * *