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:
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.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014