Professional SQL Server™ 2005 Programming

15.4. Navigating the Cursor: The FETCH Statement

I figure that whoever first created the SQL cursor syntax must have really liked dogs. They probably decided to think of the data they were after as being the bone, with SQL Server the faithful bloodhound. From this I'm guessing, the FETCH keyword was born.

It's an apt term if you think about it. In a nutshell, it tells SQL Server to "go get it boy!" With that, our faithful mutt (in the form of SQL Server) is off to find the particular bone (row) we were after. We've gotten a bit of a taste of the FETCH statement in some of the previous cursors in this chapter, but it's time to look at this very important statement more closely.

FETCH actually has many more options than what we've seen so far. Up to this point, we've seen three different options for FETCH (NEXT, PREVIOUS, and FIRST). These really aren't a bad start. Indeed, we really only need to add one more for the most basic set of cursor navigation commands, and a few after that for the complete set.

Let's look at each of the cursor navigation commands and see what they do for us:

FETCH Option	Description
NEXT	This moves you forward exactly one row in the result set and is the backbone option. Ninety percent or more of your cursors won't need any more than this. Keep this in mind when deciding to declare as FORWARD_ONLY or not. When you try to do a FETCH NEXT and it results in moving beyond the last record, you will have a @@FETCH_STATUS of −1.
PRIOR	As you have probably surmised, ...