SQL CURSOR

sql_cursor.sql

USE [colombo] 
GO 

-- drop prev version
IF EXISTS (SELECT name 
  	FROM sysobjects 
  	WHERE name = N'pco_cursor_test1' 
  	AND type = 'P')
  DROP PROCEDURE pco_cursor_test1
GO
-- create new

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
/* ============================================================== 
  Author: [ amper ] 
  Create date: [ 2007-10-12 ] 
  Description: [demo procedure for updating delivery prices 
  in Invoices table using CURSOR] 
================================================================ */ 

CREATE PROCEDURE dbo.pco_cursor_test1 
AS 
BEGIN 
  DECLARE @docno INT, @country NVarchar(50), @city NVarchar(50) 
  /* 
  define cursor on the join between Invoice and Customers tables
  */
DECLARE invoice_cursor CURSOR FOR 
   SELECT a.DocNo, b.Country, b.City 
   FROM dbo.Invoice AS a INNER JOIN dbo.Customers AS b 
   ON a.CustomerID = b.CustomerID 
   GROUP BY a.DocNo, b.Country, b.City; 

OPEN invoice_cursor   /* open cursor */ 
FETCH NEXT FROM invoice_cursor 
INTO @docno, @country, @city;

/* cursor loop */ 
WHILE (@@FETCH_STATUS <> -1) 
BEGIN 

   /* execute function that returns delivery price for 
   given location. write results to Invoice table */

   UPDATE dbo.Invoice 
   SET DlvPrice = dbo.fn_getdeliveryprice(@country, @city) 
   WHERE DocNo = @docno; 

   -- move to the next record 
   FETCH NEXT FROM invoice_cursor 
   INTO @docno, @country, @city 
END 
/* release data structures that was allocated by cursor */
CLOSE invoice_cursor; 
DEALLOCATE invoice_cursor; 

-- end of procedure 
END 
GO 

GRANT EXECUTE ON OBJECT::dbo.pco_cursor_test1 TO george; 
GO