Friday, June 8, 2012

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

No comments:

Post a Comment