SQL - Output cursor in stored procedure

USE AdventureWorks2008R2;

GO

-- Create a stored procedure

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL

DROP PROCEDURE dbo.uspCurrencyCursor;

GO

-- If the parameter is CURSOR type, it must be assigned with VARYING and OUTPUT

-- If the parameter is assigned with VARYING, the data type must be cursor with OUTPUT

CREATE PROCEDURE dbo.uspCurrencyCursor

@CurrencyCursor CURSOR VARYING OUTPUT

AS

BEGIN

SET NOCOUNT ON;

-- Set cursor value

SET @CurrencyCursor = CURSOR

-- Only forward to use fetch next to fetch records from the first row to last row.

-- Static, fetch records from tempdb, just a copy from original records, can't update.

FORWARD_ONLY STATIC FOR

SELECT CurrencyCode,

Name

FROM Sales.Currency;

OPEN @CurrencyCursor;

END

GO

-- Declare a local cursor first

DECLARE @LocalCursor CURSOR

-- Get the cursor from usp

EXECUTE dbo.uspCurrencyCursor @CurrencyCursor = @LocalCursor OUTPUT

FETCH NEXT FROM @LocalCursor

WHILE @@FETCH_STATUS = 0

BEGIN

-- Fetch next row

FETCH NEXT FROM @LocalCursor

END

-- Close cursor and deallocate it

CLOSE @LocalCursor

DEALLOCATE @LocalCursor