SQLUSA

Microsoft SQL Server 2008 Best Practices

How to architect cursor stored procedure?

 

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

 

 

 

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page