Execute the following T-SQL script in Query Editor to demonstrate the use of a cursor declared and opened for use inside a stored procedure.

USE AdventureWorks2008;

GO

CREATE PROCEDURE dbo.procSalesTerritory

    @TerritoryCursor CURSOR VARYING OUTPUT

AS

    SET @TerritoryCursor = CURSOR

    FORWARD_ONLY STATIC FOR

      SELECT TerritoryID, Territory=Name

      FROM Sales.SalesTerritory;

    OPEN @TerritoryCursor;

GO

DECLARE @RC int

DECLARE @TerritoryCursor cursor

EXECUTE @RC = [AdventureWorks2008].[dbo].[procSalesTerritory]

   @TerritoryCursor OUTPUT

WHILE (@@FETCH_STATUS = 0)

BEGIN;

     FETCH NEXT FROM @TerritoryCursor;

END;

CLOSE @TerritoryCursor;

DEALLOCATE @TerritoryCursor;

GO