Sql-docs: How to properly close and deallocate a cursor?

[feO2x]

While the docs for TSQL cursors explain how to they can be closed and deallocated, there is no coherent example where a cursor is declared, opened, used, and then closed and deallocated. This is especially difficult in the case of properly closing a cursor.

For example, in the remarks section of the DEALLOCATE docs, it says "A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope". However, in the CLOSE docs, nothing is mentioned that the cursor is closed when a cursor variable goes out of scope.

I think this leads to confusion, e.g. this StackOverflow answer says that you can simply declare a cursor variable and let it go out of scope to close and deallocate the cursor (personally, I'm not so sure if this is actually working).

Specifically, I have the following questions:

• How do you properly close a cursor? Do you have to call CLOSE and then DEALLOCATE? Is there an automatic mechanism that does this?
• Can CLOSE and DEALLOCATE throw exceptions? Should these statements be put in a separate try-catch block?
• Can you provide a comprehensive example where a cursor is declared, opened, used, and then closed and deallocated? Including the usual stuff that one needs in Stored Procedures like a transaction and try-catch blocks?
• Can you provide information what DEALLOCATE actually does internally? Is it a reference counting mechanism? The documentation of CLOSE already describes that it frees the cursor result set and cursor locks.

Please notice: this issue is based on my StackOverflow question: https://stackoverflow.com/questions/57952068/close-and-deallocate-cursor-and-try-catch-in-tsql

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 78d01993-979b-91ee-862c-55dbb00dd1dd
• Version Independent ID: b4e53b79-3356-7bd8-bcde-112e208aad9a
• Content: Cursors (Transact-SQL) - SQL Server
• Content Source: docs/t-sql/language-elements/cursors-transact-sql.md
• Product: sql
• Technology: t-sql
• GitHub Login: @rothja
• Microsoft Alias: jroth

[feO2x]

I've just read "Error and Transaction Handling in SQL Server" by Erland Sommarskog (Part 1, Part 2, and Part 3). In Part 2 Chapter 5, he talks about different behavior when a transaction is involved as well as when SET XACT_ABORT is set to ON or OFF. I don't know if this has any consequences for closing a cursor? If it has, it might be worth mentioning them in the docs, too.

[rothja]

@feO2x Thank you for these questions. I will try to find someone internally who can answer some of these questions. We might not be able to address everything here in the git issue, as this question is more in line with a forum question (which I see you've already posted on Stack Overflow, thanks!). However, from a documentation perspective, I can see how you are pointing to holes in how we document cursors, especially for CLOSE and DEALLOCATE. Please let me look into this a bit more and get back to you. Thanks, again, for the feedback!

[feO2x]

@rothja Thanks for the info. @srutzky answered the StackOverflow question and pointed out that DEALLOCATE implicitly calls CLOSE when the the cursor is not used by any other piece of code. I think this should be part of the DEALLOCATE docs.

[srutzky]

@feO2x and @rothja : I don't think I said anything about "when the cursor is not used by any other piece of code". If anything, I was saying that no other code is truly connected to a cursor, so there shouldn't be any reason that could cause a CLOSE or DEALLOCATE to error. The docs do mention something about using a cursor variable and "if there are no other references, then DEALLOCATE will de-list the cursor entirely." I could not exactly reproduce that scenario. The closest I was able to get was creating a named cursor _and_ a separate cursor variable, and then setting that variable to the named cursor's name. In that one scenario, calling DEALLOCATE on the cursor variable merely _un_-set the variable from referencing that cursor and left the variable without any reference at all (kinda like going from a value to being NULL). It's possible that the DEALLOCATE doc is wrong in its description of the behavior. I will need to do a little more testing at some point to know for certain. I have several test scripts showing a variety of scenarios for scope, locking, closing & deallocating, checking for cursor existence and status, etc that will be the basis of a detailed post on the various aspects of cursors. No ETA when I will actually get time to complete it, but I at least have a good start to it 😃 . For now, a good portion of most of my notes are encapsulated in my answer to feO2x's StackOverflow question.

Take care,
Solomon...
https://SqlQuantumLeap.com/

[rothja]

@srutzky Thanks for the detailed reply and thanks to you both for pointing me to the discussion in StackOverflow. At this point, it sounds like there is not enough conclusive evidence on exactly how the docs should be changed until we get more data. I will also try to reach out internally to see if I can find someone that knows this area well and would be able to explain how we can improve the docs.

[rothja]

Apologies for the delay, but per my previous comment, I'm not sure exactly how to proceed. For that reason, I'll close this git issue, but feel free to provide more comments on any specific change you recommend based on this discussion. I believe that @srutzky has made direct edits to the content with the edit button on the articles, so that is another route for suggestions/fixes. Thank you!