TSQL -> Using CURSOR in stored procedure (1)

Suppose that we have to update delivery price in Invoices table. We'll use stored procedure that runs CURSOR on the join between Invoice and Customers tables. We'll calculate delivery price for every record in the cursor using scalar UDF function fn_getdeliveryprice (@country, @city).

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


Execution of:
ALTER TABLE dbo.Invoice ADD DlvPrice FLOAT;
GO
EXEC pco_cursor_test1;
GO
SELECT DocNo, DlvPrice FROM dbo.Invoice;

will show results like this:
DocNoDlvPrice
128.5
26
345
48.15
....



sqlexamples.info