.NET Answers

One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.

There are actually two parts to this question.  First, you should always try to find some other way of doing whatever it is you think you need to do by processing one row at a time.  SQL is a “Set-Based” language and as such it expects you to work with the whole set.  That’s what it was designed to do best and that’s why it has functions such as MAX, MIN, SUM, AVG, etc.  Many of these functions in combination with a good WHERE clause will get you the information you need.

However, there are times when you’ll need to process the information one record at a time, and this is what the CURSOR was created for.

Before you use a CURSOR, you’ll need to declare it.  The basic form looks like:

DECLARE cursorName CURSOR for
 SELECT field1, field2 FROM myTable
 WHERE whereClause

Notice that unlike other DECLARE statements in SQL, we did not prefix the variable with an ‘@’

Once we have the cursor declared, we need to OPEN it to cause it to execute:

OPEN cursorName

The rest is going to look very similar to what we did back when we were using ODBC.  To move to the first record, we have to FETCH the row:

FETCH NEXT FROM cursorName
 INTO @field1, @field2

Obviously, we would have declared @field1 and @field2 ahead of time.  Notice that @field1 and @field2 correspond to the order the fields show up in the SELECT statement above.  You can call the variables whatever you want, but it is customary to name them the same as the fields you are getting the data from.

There is a special variable in SQL called @@FETCH_STATUS that will be zero (0) as long as the fetch was able to retrieve a row, so you’ll want to set up a while loop after your first fetch that checks the status and then processes the data.  Right before the end of the while loop you will issue another FETCH.

FETCH NEXT FROM cursorName
 INTO @field1, @field2
 While @@FETCH_STATUS = 0
 Begin
  /* do something with
     the data here */
  FETCH NEXT FROM cursorName
   INTO @field1, @field2
 End

Once you know how, it isn’t that hard to process records in your stored procedures which will allow you to do a significant amount of your data processing on the SQL server and return to the client only the information that the client needs.