Cursors = Old School and Bad (It Depends!)

After reading through some site comments, I decided to get to the bottom of this issue: Are cursors really bad? Well... it depends. Cursors are bad if you are running SQL 2000 and SQL 2005 prior to the SP2 update. Since then, they have been optimized to run just as well as a WHILE loop. Peronally, I work on servers from 2000 to 2008 and I want to keep things standardized across the versions, so I just use WHILE loops to cycle through rows. Below we will go over the implementation of WHILE loops so you can compare previous and past rows to the current one.

One thing I will say about looping in general, try not to do it if you don't have too. The best option would be to perform your calculations on all of the rows at once.

Let's go over the implementation of loops.

---

Cycling Through a Table 1 Row at a Time

If you ever find yourself in a spot for cycling through rows, here is how you do it:

1. Create a temp table with an identity column that auto-increments, starting at 1 and going up by 1.
  CREATE TABLE #Temp1 (
    ID INT Identity(1,1),
    ItemName VARCHAR(50),
    ItemDescription VARCHAR(50)
  )

2. Get the data inserted for review (you do not need to insert the ID field, it is being inserted with numbers increasing by 1 and starting at 1).
  INSERT INTO #Temp1(
    ItemName,
    ItemDescription
  )
  SELECT ItemName,
    ItemDescription
  FROM InventoryTable

3. Create the loop and cycle through the data.
  DECLARE @Counter INT
  SET @Counter = 1

  DECLARE @ItemReview VARCHAR(50)

  WHILE @Counter <= (SELECT COUNT(*) FROM InventoryTable)
  BEGIN
    SELECT @ItemReview = t.ItemName
    FROM #Temp1 t
    WHERE t.ID = @Counter

    IF @ItemReview = 'Tomatoes'
    BEGIN
        Do something here….
    END

    SET @Counter = @Counter + 1

  END

Notice how we use a @Counter variable to cycle through the rows on the table by matching up against the ID field that was auto-incremented when we inserted the data. This allows us to go through every row on the table and find the word "Tomatoes".

---