SQL CURSOR – SQL For Programmers

ka_vol1_143 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.

Related Post

  • SQL CURSOR Performance – SQL For ProgrammersSQL CURSOR Performance – SQL For Programmers Last week I showed how to use the SQL CURSOR to loop through records in a database.  In that article I mentioned that you want to avoid using the CURSOR if you can because it has […]
  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct. Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

2 Pingbacks/Trackbacks

Awards & Certs