There are many times we need to examine or even alter fields of certain records of a result set returned by a query. You can certainly achieve this using some (usually quite complex) SQL commands. However you should remember that an SQL command could be expensive in term of speed and resource utilization. Even more for complex SQL commands.
The answer to this is to utilize cursor.
This tutorial will introduce you to SQL Server cursor. Note that while you could update a table using cursor, this tutorial will not touch that area. We might discuss it in future article, though. In this tutorial we only discuss read-only cursors.
In utilizing cursor (in read-only mode), I usually use the following five steps.
- Create the cursor
- Open the cursor
- Browse the cursor
- Close the cursor
- Deallocate the cursor
We create a cursor by declaring it. To declare a cursor we need to use DECLARE CURSOR command. Visit this page (or your local Book Online installation) to get details of this command. The basic need of DECLARE CURSOR is the name of the cursor and SELECT statement that will return the wanted result set.
For example, this is the basic need of declaring a cursor.
DECLARE CURSOR MyCursor FOR select * from MyTableIn the above code, I declared a cursor with name of MyCursor and it will be "populated" with records returned by "select * from MyTable" query.
Since we are interested only for read-only cursor, we can add this "option" when declaring the cursor and it will actually optimize the speed when we are browsing the cursor. Since the cursor will only be read-only, SQL Server engine does not need to track and synchronize changes. Lesser used resource means faster operation (at least usually).
To make the cursor read-only, you can add READ_ONLY and/or STATIC argument when declaring the cursor. You can also use FAST FORWARD argument. Actually FAST_FORWARD is combination of READ_ONLY and FORWARD_ONLY arguments. As the name suggested, cursor declared as FORWARD_ONLY (or FAST_FORWARD) can only be browsed in one direction, from the first record to the last. You can not go back to prior record. The opposite of FORWARD_ONLY is SCROLL. With SCROLL you can go to any record as long as the cursor is opened.
If your task will only browse the records in one direction, from the first to the last record, the FAST_FORWARD argument sounds like the best option.
DECLARE CURSOR READ_ONLY STATIC MyCursor FOR select * from MyTableor
DECLARE CURSOR FAST_FORWARD MyCursor FOR select * from MyTable
2. Open a Cursor
This operation is straightforward. Just issue OPEN command followed with the cursor's name, and you will be alright (as long as the SELECT statement was valid). Like this:
OPEN MyCursor3. Browse a Cursor
We browse a cursor by fetching the records. The command to use is (yes, you've guessed it) FETCH. In this context, fetching means to make the fetched record the active/current record and optionally load the record's field values into the given local variables. Note that while it's optional, but most fetching operations involve loading the fields values to local variables.
We could fetch next or previous record of the current one. We could also fetch record using its relative position from the current one. For example, you can fetch record that is 3 records after the current one, or 3 records before the current one. You can also fetch record using its absolute position. When using positive number, this absolute position is the same with record number. For example, first record's absolute position would be 1, absolute position of 5 would be the fifth record from the beginning. When you gave negative value for absolute position, the absolute position will be based on the end of the cursor. For example, -1 will be the last record.
However, depend on the conditions you used when you declare the cursor, you might only be able to fetch next record. For example, if you declare FAST_FORWARD you will only be able to fetch next record. When you specifically use SCROLL you might be able to use all fetch options. Visit this page (scroll down to the Remark section) to get details about this.
After fetching, you should check whether it has been done successfully. There are several ways to do this. But the simplest one is by calling @@FETCH_STATUS function. This function will return the result of the last fetch operation. Your last fetch operation was a successful one if @@FETCH_STATUS returned 0. Other value would indicate a failure.
When using forward only cursors, I usually use code pattern like this.
-- Declare local variables to capture fetched record's values DECLARE @Field1Val nvarchar(50), @Field2Val nvarchar(50); -- Create the cursor DECLARE MyCursor CURSOR FAST_FORWARD FOR select Field1, Field2 from MyTable where blah blah..; -- Open the cursor; OPEN MyCursor; -- Fetch the first record FETCH NEXT FROM MyCursor INTO @Field1Val, @Field2Val; -- Check the fetch operation result, if it was a success, continue into the block WHILE @@FETCH_STATUS=0 BEGIN -- Do things you want to do with @Field1Val and @Field2Val ... ... -- Fetch next record FETCH NEXT FROM MyCursor INTO @Field1Val, @Field2Val; END;4. Close a Cursor
Similar with opening, closing a cursor is a pretty straightforward operation. Just issue (yea, you've guessed it, again!) CLOSE command followed by the cursor's name. Something like this:
CLOSE MyCursor5. Deallocate a Cursor
Deallocating a cursor also another straightforward operation. Just issue DEALLOCATE followed by the cursor's name. Just like this:
So my complete code pattern for forward only cursor is like this.
-- Declare local variables to capture fetched record's values DECLARE @Field1Val nvarchar(50), @Field2Val nvarchar(50); -- Create the cursor DECLARE MyCursor CURSOR FAST_FORWARD FOR select Field1, Field2 from MyTable where blah blah..; -- Open the cursor; OPEN MyCursor; -- Fetch the first record FETCH NEXT FROM MyCursor INTO @Field1Val, @Field2Val; -- Check the fetch operation result, if it was a success, continue into the block WHILE @@FETCH_STATUS=0 BEGIN -- Do things you want to do with @Field1Val and @Field2Val ... ... -- Fetch next record FETCH NEXT FROM MyCursor INTO @Field1Val, @Field2Val; END; -- Close the cursor CLOSE MyCursor; -- Deallocate the cursor DEALLOCATE MyCursor;