SQL Screw-ups: Cursors and While Loops

This is a continuation of the SQL Screw-ups series that stemmed from my Nashville .NET User Group talk on 03/14/2019. Slides and setup details are on the first post in the series.

The rule of thumb around cursors and while loops in SQL is simply don’t do it unless you must. Let’s look at a scenario in which you should not use a loop, and then we’ll look at a problem that is easily solved by a loop.

A colleague came to me earlier in my career with a SQL script intended to backfull some data for a new column he/she had added. To be honest, I had never used a while loop in SQL. I didn’t know how to write one or what a good use case would even be. Since I still don’t know how to use a cursor or a while loop, I’ll demonstrate the gist of the script in pseudocode.

-- declare cursor from a select with no predicates (full table)
-- loop all rows and set the value of the new column to a constant value

/* here is something similar using a cursor (not complete or syntactically correct) */
DECLARE CustomerCursor CURSOR FOR
SELECT Sales.Customer.CustomerID
INTO #CustomersToUpdate
FROM Sales.Customer
WHERE Customer.TerritoryID = 2

OPEN CustomerCursor
 -- insert code to update each customer's territory to 10
CLOSE CustomerCursor;  

DEALLOCATE CustomerCursor; 
GO

After I asked my colleague to explain his/her goal at a high level, I asked him/her to explain the SQL that I was reviewing. He/She simply needed to set the column to a constant value. I really enjoy the fact that a developer can skin the same cat in many different ways, but this was truly overkill, and it was an opportunity for me to teach my colleague something about SQL. It’s set-based. You don’t have to loop the rows and update them individually. Here is the solution we arrived at together after talking the problem through.

UPDATE Sales.Customer
SET TerritoryID = 10
WHERE TerritoryID = 2

With the update statement, we can simply update all the records at once. We can even use logic for the value per row if we need something other than the same value for all rows.

Let’s look at a potentially valid use case for a while loop. In this example, we’ll write a function to build a table consisting of dates within a range. This seems like a decent use of a while loop. I’m not sure how else you’d implement this solution. I welcome suggestions in the comments.

/* 
    Function to return a resultset of all Dates between Start and
    End Date, including the Start and End Dates
 */
CREATE FUNCTION Sales.GenerateDatesForRange(
  @StartDate    datetime,
  @EndDate      datetime)
RETURNS @DateTable TABLE (
  [Date]       datetime 
)
AS
BEGIN

  WHILE @startDate <= @endDate
  BEGIN
    INSERT INTO @DateTable ([Date]) VALUES (@startDate)

    -- add one day to the previous date that we inserted
    SET @startDate = DATEADD(day, 1, @startDate)
  END

  RETURN
END
GO

So, there you have it. Definitely use a while loop or a cursor when appropriate. But also definitely do not use a while loop or a cursor when not
appropriate. They are typically more difficult to read and debug, and they can be less performant than set-based operations.

I’d be interested to learn about uses for cursors or while loops in the comments.