TSQL游标如何检查是否已经声明并因此释放

Question

在尝试再次打开游标之前，如果游标已存在，如何确保将其释放？

对于表，我可以使用类似的东西：

if exists (select top 1 from tempdb.sys.tables where name = '##tmpTable') 
    drop table ##tmpTable;  
... then I can recreate my ##tmpTable 

但我无法弄清楚如何为光标执行此操作，就像

-- First clean up if already exists..
  .....                                  <----- what goes here???

-- Declare and use a cursor
DECLARE  someCursorName CURSOR 
 FOR 
  select something from somewhere
 FOR READ ONLY

我这样做是为了确保我的脚本在开始工作之前清理干净

我能想到的最好的是：

begin try DEALLOCATE someCursorName ; end try begin catch end catch

是这样的一个好的做法？

编辑： 这是维护脚本。在我们大量客户定制的数据库中，可能有许多表，游标用于跨表运行统计分析 - 根据表的类型，会发生不同的情况。基本上有很多动态sql。如果脚本失败，我希望能够重复该工作，而不必担心手动干预。这里只有一层范围。

像所有事情一样，我很高兴用设置操作替换光标。这些是游标循环所做的事情：

• 构造sql来重组/重建索引（最初有手动sql来确定要运行的DDL，然后发出DDL）
• 分析数据分布和错误不同的表
• 在日志中查找错误并查找适当的表并获取该数据（最初有手动sql来确定错误所在的位置，然后剪切并粘贴模板以根据错误类型查找错误）

Answer 1

您可以将游标声明为变量，然后当它超出范围时它将自动关闭并释放。下面是将其与动态 SQL 结合使用的示例。

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   master..spt_values
    WHERE  name <> ''
    ORDER  BY name;

OPEN @C1;

EXEC sp_executesql N'
DECLARE @name VARCHAR(50)
FETCH NEXT FROM @C1 INTO @name;

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT @name

      FETCH NEXT FROM @C1 INTO @name;
  END 
', N'@C1 CURSOR', @C1 = @C1

Answer 2

创建 CURSOR 变量，然后使用 SET 定义它时，当变量超出范围（即批处理或子流程结束）时，确实会自动释放它，这种构造也是完全没有必要的。

假设您没有将游标本身传递到进程的各个嵌套级别，或者需要它在批次之间生存，那么最简单的方法（可能也是最好的总体方法）是将游标声明为 LOCAL。虽然可以在 LOCAL 和 GLOBAL 之间配置未指定，但如果未指定，则默认设置为游标为 GLOBAL。在问题的代码中，没有 LOCAL 关键字，因此我们可以假设光标是 GLOBAL 这就是为什么需要清理先前运行的问题甚至出现的原因向上。因此，只需将 LOCAL 关键字添加到游标声明中即可。

现在，对于那些您实际上想要一个GLOBAL游标并因此需要首先检查以确保它尚未被声明的情况，有两种简单的方法可以对此进行测试：

1. 从 sys.dm_exec_cursors DMF（类似于对表使用 sys.tables）
2. 使用 CURSOR_STATUS 函数可以告诉你它是否存在，以及如果是，则它是打开还是关闭，如果打开，则是否有 0 行或多于 0 行。

我还在 SQL Server 2005 SP4 上进行了测试，发现上述所有三项的行为方式也相同。