SQL 如何检查游标是否存在（打开状态）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7430560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if cursor exists (open status)
提问by Maddy
How do I check if a cursor is open or not? Because many times I am encountering the error 'Cursor already exists'. Please let me know how can I check whether a cursor is already in open status.
如何检查游标是否打开？因为很多时候我都遇到错误“光标已经存在”。请让我知道如何检查游标是否已处于打开状态。
In fact I have closed as well as Deallocated it at the end (CLOSE ppm_cursor; DEALLOCATE ppm_cursor;) But Still i am getting the same error what could be the reason.
事实上，我已经关闭并在最后释放了它（CLOSE ppm_cursor; DEALLOCATE ppm_cursor;）但我仍然遇到同样的错误，这可能是什么原因。
回答by Gary W
You can use the CURSOR_STATUSfunction to determine its state.
您可以使用CURSOR_STATUS函数来确定其状态。
IF CURSOR_STATUS('global','myCursor')>=-1
BEGIN
DEALLOCATE myCursor
END
回答by Prateek
Close the cursor, if it is empty then deallocate it:
关闭游标，如果它为空则释放它：
IF CURSOR_STATUS('global','myCursor') >= -1
BEGIN
IF CURSOR_STATUS('global','myCursor') > -1
BEGIN
CLOSE myCursor
END
DEALLOCATE myCursor
END
回答by Muhammad Omar ElShourbagy
Just Small change to what Gary W mentioned, adding 'SELECT':
对 Gary W 提到的内容稍加改动，添加“SELECT”：
IF (SELECT CURSOR_STATUS('global','myCursor')) >= -1
BEGIN
DEALLOCATE myCursor
END
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/eb268010-75fd-4c04-9fe8-0bc33ccf9357
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/eb268010-75fd-4c04-9fe8-0bc33ccf9357
回答by Tom Regan
I rarely employ cursors, but I just discovered one other item that can bite you here, the scope of the cursor name.
我很少使用游标，但我刚刚发现了另一项可以让您感到困惑的项目，即游标名称的范围。
If the database CURSOR_DEFAULT is global, you will get the "cursor already exists" error if you declare a cursor in a stored procedure with a particular name (eg "cur"), and while that cursor is open you call another stored procedure which declares and opens a cursor with the same name (eg "cur"). The error will occur in the nested stored procedure when it attempts to open "cur".
如果数据库 CURSOR_DEFAULT 是全局的，并且在具有特定名称（例如“cur”）的存储过程中声明游标，并且在该游标打开时调用另一个声明的存储过程，则会收到“游标已存在”错误并打开一个同名的游标（例如“cur”）。嵌套存储过程在尝试打开“cur”时会发生该错误。
Run this bit of sql to see your CURSOR_DEFAULT:
运行这段 sql 以查看您的 CURSOR_DEFAULT：
select is_local_cursor_default from sys.databases where name = '[your database name]'
If this value is "0" then how you name your nested cursor matters!
如果此值为“0”，那么您如何命名嵌套游标很重要！
回答by Aaron
This happened to me when a stored procedure running in SSMS encountered an error during the loop, while the cursor was in use to iterate over records and before the it was closed. To fix it I added extra code in the CATCH block to close the cursor if it is still open (using CURSOR_STATUS as other answers here suggest).
当在 SSMS 中运行的存储过程在循环期间遇到错误时，这发生在我身上，而游标正在用于迭代记录并在它关闭之前。为了修复它，我在 CATCH 块中添加了额外的代码以在游标仍然打开时关闭游标（使用 CURSOR_STATUS 作为此处建议的其他答案）。
