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

-- T-SQL cursor stored procedure - declaring a cursor in a stored procedure

-- TRANSACT-SQL cursor local variable

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

                             ORDER BY Territory;

  OPEN @TerritoryCursor;

GO

DECLARE  @RC INT, @TerrID INT, @TerrName VARCHAR(64)

DECLARE  @TerritoryCursor CURSOR  -- CURSOR variable

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

  @TerritoryCursor OUTPUT

FETCH NEXT FROM @TerritoryCursor INTO @TerrID, @TerrName;

WHILE (@@FETCH_STATUS = 0)

  BEGIN

    PRINT CONVERT(varchar,@TerrID)+'  '+@TerrName

/*

9  Australia

6  Canada

3  Central

7  France

8  Germany

2  Northeast

1  Northwest

5  Southeast

4  Southwest

10  United Kingdom

*/

    FETCH NEXT FROM @TerritoryCursor INTO @TerrID, @TerrName;

  END;

CLOSE @TerritoryCursor;

DEALLOCATE @TerritoryCursor;

GO

DROP PROC dbo.procSalesTerritory