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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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