DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect cursor stored procedure?

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE