Using Cursors in SQL Server 2000 （ZT）

转自：http://blog.joycode.com/mvm/archive/2005/09/07/63084.aspx

In a lot of places, cursors are used to traverse through a recordset. However, a lot of cursors are defined like this:

DECLARE @myCursor CURSOR FOR SELECT … FROM TABLE
OPEN @myCursor

-- do something

CLOSE @myCursor
DEALLOCATE @myCursor

The above code has several problems:

1. The CURSOR is defined as a GLOBAL cursor, meaning that several sessions can access the same cursor. If two sessions happen to execute the same proc at the same time, one of them will fail with 'CURSOR ALREADY EXISTS' error.
2. If an exception is raised, the CLOSE/DEALLOCATE is never called, causing a memory leak. The memory is cleaned up when the session ends.
3. GLOBAL cursors are the default, so if you do not specify that you want a LOCAL cursor, you get a global cursor.

To avoid these problems, always use LOCAL cursors. LOCAL cursors are defined at session scope, and get automatically deallocated and closed when the cursor goes out of scope.

So the above should be:

DECLARE @myCursor CURSOR LOCAL FOR SELECT * FROM TABLE
OPEN @myCursor

-- close/deallocate not needed as SQL will automatically clean up for you