DECLARE @AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1FETCH NEXT FROM c1
INTO @AuthorIDWHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorIDEND
CLOSE c1
DEALLOCATE c1
The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor.? You can do just about anything here that you can do in a SELECT statement.? The OPEN statement statement executes the SELECT statement and populates the result set.? The FETCH statement returns a row from the result set into the variable.? You can select multiple columns and return them into multiple variables.? The variable @@FETCH_STATUS is used to determine if there are any more rows.? It will contain 0 as long as there are more rows.? We use a WHILE loop to move through each row of the result set.
In this example, I just print the contents of the variable.? You can execute any type of statement you wish here.? In a recent scrīpt I wrote I used a cursor to move through the rows in a table and call a stored procedure for each record passing it the primary key.? Given that cursors are not very fast and calling a stored procedure for each record in a table is also very slow, my scrīpt was a resource hog.? However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem.? In this case, I might have something like this:
EXEC spUpdateAuthor (@AuthorID)
instead of my Print statement. The CLOSE statement releases the record set and the DEALLOCATE statementreleases the resources associated with a cursor.
If you are going to update the records as you go through them, you can use the UPDATE clause when you declare a cursor.
DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname
You can code your UPDATE statement to update the current record in the
cursor like this
UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
If you have a cursor that will be read only you can speed it up by using the READ_ONLY clause:?
DECLARE c1 CURSOR READ_ONLY FOR
SELECT au_id
FROM authors
That covers the basics of cursors.? You can check Books Online for more detailed information.