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
