SQL Cursors - how to avoid them

Introduction

There may be times when you need to loop through a resultset a row at a time and perform a certain action for each row. The most obvious way to solve this task is to use a SQL Cursor.

Whilst cursors may seem like a good idea they can often cause your database application problems as they can lock the tables that are used to populate the cursor whilst the rows in the cursor are looped through. Depending on the action that you are performing on each row this can take a considerable time. The effect of this is that tables cannot be updated or accessed by other users whilst the cursor is open.

I know one SQL specialist who when interviewing for DBA roles asks the candidate to write the syntax for using a cursor. If the candidate knows it then it is a negative point against them as they shouldn't use cursors frequently enough so that they can remember the syntax.

Whilst many SQL books advise you not to use SQL cursors, not many provide alternative solutions. This article shows how alternatives to cursors can be implemented.

An example of a SQL Cursor that we want to avoid

DECLARE @CustomerID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
-- declare cursor called ActiveCustomers
DECLARE ActiveCustomers Cursor FOR
 SELECT CustomerID, FirstName, LastName 
 FROM Customer
 WHERE Active = 1
-- Open the cursor
OPEN ActiveCustomers
-- Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
-- perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
 Exec MyStoredProc @CustomerID, @Forename, @Surname
 -- get next row of cursor
 FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
END
-- Close the cursor to release locks
CLOSE ActiveCustomers
-- Free memory used by cursor
DEALLOCATE ActiveCustomers

Cursor alternative 1: Using the SQL WHILE loop

SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #ActiveCustomer (
 RowID int IDENTITY(1, 1), 
 CustomerID int,
 FirstName varchar(30),
 LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #ActiveCustomer (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1 

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName 
 FROM #ActiveCustomer
 WHERE RowID = @RowCount

 EXEC MyStoredProc @CustomerID, @FirstName, @LastName

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveCustomer

We can see the above code gives the same functionality as the first code example but without using a cursor. This gives us the benefits that the Customer table is not locked as we are looping through our resultset so other queries on the Customer table that are submitted by other users will execute much faster. We will also have a faster operating SQL script by avoiding cursors which are slow in themselves.

Cursor Alternative 2: Using User Defined Functions

Cursors are sometimes used to perform a calculation on values that come from each row in its rowset. This scenario can also be achieved by replacing a Cursor with a User Defined Function. An example of a User Defined Function performing a calculation is given below:

-- return a discount %age that the customer 
-- can recieve based on their no. and value
-- of purchases
CREATE FUNCTION  dbo.GetDiscountLevel(
 @CustomerID int
)
RETURNS int
AS
BEGIN
 DECLARE @DiscountPercent int
 DECLARE @NumberOrders int, @SalesTotal float
 
 SELECT @NumberOrders = COUNT(OrderID), 
  @SalesTotal = SUM(TotalCost)
 FROM Sales
 WHERE CustomerID = @CustomerID 
 
 IF @SalesTotal > 5000.00 AND @NumberOrders > 5
  SET @DiscountPercent = 5
 ELSE
 BEGIN
  IF @SalesTotal > 3000.00 AND @NumberOrders > 3
   SET @DiscountPercent = 3
  ELSE
   SET @DiscountPercent = 0 
 END
 
 Return @DiscountPercent
END

An example of this function being used to replace a cursor might look something like:

SELECT FirstName, LastName, dbo.GetDiscountLevel(CustomerID) As DiscountPercent
FROM Customer

Conclusion

In this article we have seen how SQL Cursors can cause performance problems and affect other queries by locking tables. We have demonstrated two popular ways of avoiding the use of cursors. The WHILE loop does avoid the use of a Cursor but it still uses an iterative loop. The User Defined Function option keeps our SELECT query tidy and enables us to perform calculations using column values from our SELECT statement.