Tip Regarding Cursor Variables

In one of the recent conferences I sat down with a couple of friends—Umachandar Jayachandran (UC) and Ron Talmage; we were trying to resolve a performance problem related to cursors. As UC started writing the cursor code in his laptop, I realized that he used cursor syntax that I wasn’t used to. Syntax that is not new in SQL Server, but that I just wasn’t aware of. Could very well be that I’m one of very few who weren’t aware of this syntax, but for the chance it’s not the case, I decided to share.

When you need to define a local cursor that you will work with only in the context of the local batch, you can define a cursor variable, and work with it the same way you work with regular cursors. The benefit in using a local variable is that once the batch expires, the variable is destroyed, and therefore the cursor is automatically closed and deallocated. So using a cursor variable can save you from writing the two lines of code that close and deallocate the cursor. ;)

As an example, run the following code to create a table called Sales in tempdb and populate it with sample data:

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.Sales', 'U') IS NOT NULL DROP TABLE dbo.Sales;

CREATE TABLE dbo.Sales

(

  empid INT      NOT NULL,

  dt    DATETIME NOT NULL,

  qty   INT      NOT NULL,

  CONSTRAINT PK_Sales PRIMARY KEY(empid, dt)

);

GO

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(1, '20090212', 5);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(1, '20090213', 11);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(1, '20090214', 19);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(1, '20090215', 19);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(1, '20090216', 3);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(2, '20090212', 13);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(2, '20090213', 23);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(2, '20090214', 2);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(2, '20090215', 17);

INSERT INTO dbo.Sales(empid, dt, qty) VALUES(2, '20090216', 7);

Suppose that you need to write code that returns the running total quantity for each employee and day, and decide to use a cursor-based solution. Here’s how your code would look like using a local cursor variable:

DECLARE @Result TABLE

(

  empid  INT,

  dt     DATETIME,

  qty    INT,

  sumqty BIGINT

);

DECLARE

  @C        AS CURSOR,

  @empid    AS INT,

  @prvempid AS INT,

  @dt       AS DATETIME,

  @qty      AS INT,

  @sumqty   AS BIGINT;

SET @C = CURSOR FAST_FORWARD FOR

  SELECT empid, dt, qty

  FROM dbo.Sales

  ORDER BY empid, dt;

OPEN @C;

FETCH NEXT FROM @C INTO @empid, @dt, @qty;

SELECT @prvempid = @empid, @sumqty = 0;

WHILE @@fetch_status = 0

BEGIN

  IF @empid <> @prvempid SELECT @prvempid = @empid, @sumqty = 0;

  SET @sumqty = @sumqty + @qty;

  INSERT INTO @Result VALUES(@empid, @dt, @qty, @sumqty);

  FETCH NEXT FROM @C INTO @empid, @dt, @qty;

END

SELECT * FROM @Result;

Again, this syntax is not new in the product, and this code runs fine even on SQL Server 2000.

Of course, if you have a long batch and are done with the cursor before the batch expires, it is still recommended to close and deallocate the cursor explicitly to free its resources. Otherwise, as programmers we always appreciate any savings in number of keystrokes.

Cheers,

BG