How to Perform SQL Server Row-by-Row Operations Without Cursors

SQL cursors have been a curse to database programming for many years because of their poor performance. On the other hand, they are extremely useful because of their flexibility in allowing very detailed data manipulations at the row level. Using cursors against SQL Server tables can often be avoided by employing other methods, such as using derived tables, set-based queries, and temp tables. A discussion of all these methods is beyond the scope of this article, and there are already many well-written articles discussing these techniques.

The focus of this article is directed at using non-cursor-based techniques for situations in which row-by-row operations are the only, or the best method available, to solve a problem. Here, I will demonstrate a few programming methods that provide a majority of the cursor’s flexibility, but without the dramatic performance hit.

Let’s begin by reviewing a simple cursor procedure that loops through a table. Then we’ll examine a non-cursor procedure that performs the same task.

In this example, i use a table:

tblCustomers

{

CustomerNo varchar (10) (PK),

CustomerName nvarchar(50)

}

With using cursor for loops through the table tblCustomers:

-- declare all variables!

DECLARE @CustomerNo varchar(10),

@CustomerName nvarchar(50)

-- declare the cursor

DECLARE Customer_Cursor CURSOR FOR

SELECT CustomerNo,

CustomerName

FROM tblCustomers

OPEN Customer_Cursor

FETCH Customer_Cursor INTO @CustomerNo, @CustomerName

-- start the main processing loop.

WHILE @@FETCH_STATUS = 0

BEGIN

-- This is where you perform your detailed row-by-row

-- processing.

-- Example:

PRINT 'Customer No: ' + @CustomerNo + '; Customer Name: ' + @CustomerName

-- Get the next row.

FETCH Customer_Cursor INTO @CustomerNo, @CustomerName

END

CLOSE Customer_Cursor

DEALLOCATE Customer_Cursor

As you can see, this is a very straight-forward cursor procedure that loops through a table called tblCustomers and retrieves CustomerNo and CustomerNumber for every row. Now we will examine a non-cursor version that does the exact same thing:

-- declare all variables!

DECLARE @CustomerNo varchar(10),

@CustomerName nvarchar(50)

DECLARE @RowCount int DECLARE @RowIndex int

SELECT @RowCount = COUNT(CustomerNo) FROM tblCustomers

SET @RowIndex = 1 IF (@RowCount > 0)

BEGIN

WHILE (@RowIndex <= @RowCount) BEGIN SET ROWCOUNT @RowIndex SELECT @CustomerNo = CustomerNo, @CustomerName = CustomerName FROM tblCustomers SET @RowIndex = @RowIndex + 1 -- This is where you perform your detailed row-by-row

-- processing.

-- Example:

PRINT 'Customer No: ' + @CustomerNo + '; Customer Name: ' + @CustomerName END--end while

END