Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provides different types of cursors to support different type of scrolling options.
When we use a Select statement in a stored procedure to get some result, we can get a collection of all the records which satisfy the given criteria. This result set can be returned back as a whole to the client application. We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors.
In order to work with a cursor we need to perform some steps in the following order
1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor
So, let’s take a look at these steps in a little detail
When we use a Select statement in a stored procedure to get some result, we can get a collection of all the records which satisfy the given criteria. This result set can be returned back as a whole to the client application. We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors.
In order to work with a cursor we need to perform some steps in the following order
1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor
So, let’s take a look at these steps in a little detail
First of all we need to declare a cursor with the help of Declare statement and in order to specify the result set contained by this cursor we use Select statement. For example we can define a cursor named MyCur and we can use a table named Usershaving two columns Username, and Password
Declare MyCur Cursor for select * from Users
Next, we need to open the cursor so that we’ll be able to use it
Open MyCur
Now, fetch first row from this cursor and loop through the cursor records until the specified criteria is found
Declare @Username varchar(50)Declare @Password varchar(50)
Fetch Next From Cursor Into @Username,@Password
While @@Fetch_Status=0
Begin
--Check if appropriate row was found then process it
--Otherwise
--Fetch the next row as we did in the previous fetch statement
End
When we have worked with the cursor, we’ll close it and deallocate it so that there will remain no reference to this cursor any more.
Close MyCur
Deallocate MyCur
Note: Fetch_Status is the default parameter to cursor While @@Fetch_Status=0 indicates until last record