Professional SQL Server™ 2005 Programming

15.2. The Lifespan of a Cursor

Cursors have lots of little pieces to them, but I think that it's best if we get right into looking first at the most basic form of cursor and then build up from there.

Before we get into the actual syntax though, we need to understand that using a cursor requires more than one statement — indeed, it takes several. The main parts include:

• The declaration

• Opening

• Utilizing/navigating

• Closing

• Deallocating

That being said, the basic syntax for declaring a cursor looks like this:

DECLARE <cursor name> CURSOR
FOR <select statement>

Keep in mind that this is the super-simple rendition — create a cursor using defaults wherever possible. We'll look at more advanced cursors a little later in the chapter.

The cursor name is just like any other variable name, and, other than not requiring the "@" prefix, they must obey the rules for SQL Server naming. The SELECT statement can be any valid SELECT statement that returns a result set. Note that some result sets will not, however, be updatable. (For example, if you use a GROUP BY, then what part of the group is updated? The same holds true for calculated field for much the same reason.)

We'll go ahead and start building a reasonably simple example. For now, we're not really going to use it for much, but we'll see later that it will be the beginning of a rather handy tool for administering your indexes:

DECLARE @SchemaName varchar(255) DECLARE @TableName varchar(255) DECLARE @IndexName varchar(255) DECLARE @Fragmentation ...