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
|