|
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
|