TSQL游标如何检查是否已经声明并因此释放 [英] TSQL Cursor how to check if already declared and thus deallocate

问题描述

如果在尝试再次打开游标之前，如何确定已解除游标的存取状态？

对于表格，我可以使用： / p>

 如果存在（从tempdb.sys.tables中选择前1个，其中name ='## tmpTable'）
 drop table ## tmpTable; 
 ...然后我可以重新创建我的## tmpTable 
  

但我不能工作out如何做一个游标像

   - 首先清理，如果已经存在.. 
 ... ..< -----这是什么？ 
 
  - 声明并使用游标
 DECLARE someCursorName CURSOR 
 FOR 
从某处选择内容
 FOR READ ONLY 
  
 
 
 
我这样做是为了确保我的脚本在开始工作之前清理。
 
 
 
最好的我可以想出是：
 开始尝试DEALLOCATE someCursorName;这是一个很好的做法吗？
 
 
 
 
 
 
 $ b < b $ b 
编辑：
这是maintennance脚本。在我们大量客户定制的数据库中，可以有许多表，并且光标用于跨表运行统计分析 - 这取决于表的类型不同的事情发生。基本上很多动态sql。如果脚本失败，我想能够重复这个工作而不用担心手动干预。这里只有一个级别的范围。
 
 
 
像所有的东西，我很乐意用set操作来替换游标。这些是光标循环执行的事情：
 
 
 
 
 
构造sql以reorg / rebuild索引（或者直接有手动sql来确定运行DDL，然后发出DDL） 
 
 
分析不同表格中的数据差错和错误
 
 
查找日志中的错误并查找适当的表并抓取该数据（或者原来有手动sql来确定错误位置，然后剪切和粘贴模板以根据错误类型查找错误）  / li> 
 
 
 
解决方案
 您可以将Cursor声明为变量，然后关闭并释放当它超出范围时自动。 
  DECLARE @ C1 AS CURSOR; 
 
 SET @ C1 = CURSOR FAST_FORWARD 
 FOR SELECT name 
 FROM master..spt_values 
 WHERE name<> ''
 ORDER按名称; 
 
 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 
  
 
How can I make sure that I deallocate a cursor if it already exists before I try and open it again?

For a table I can use something like:
if exists (select top 1 from tempdb.sys.tables where name = '##tmpTable') 
    drop table ##tmpTable;  
... then I can recreate my ##tmpTable 
But I can't work out how to do it for a cursor like
-- 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
I'm doing this to ensure that my script cleans up before it starts work

Best I can come up with is :
begin try DEALLOCATE someCursorName ; end try begin catch end catch
Is this a good practice?

EDIT: 
This is maintennance script. In our heavily customer customised databases there can be many tables and the cursor is used to run statistical analyses across the tables - depending on the types of tables different things happen. Basically lots of dynamic sql. If the script fails I'd like to be able to repeat the job without worrying about manual intervention. There is only one level of scope here.

Like all things I'm happy to replace the cursors with set operations. These are the things that the cursors loops do:


construct sql to reorg/rebuild indexes (orginally there was manual sql to determine the DDL to run, and then the DDL was issued)
analyse data spreads and errors in different tables
find errors in logs and look up appropriate tables and grab that data  (orginally there was manual sql to determine the places where errors where and then cut and paste template(s) to look up the errors dependant upon types of error)

解决方案
You can declare the Cursor as a variable then it will be closed and deallocated automatically when it goes out of scope. Example of using this in conjunction with dynamic SQL below.
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


                        
这篇关于TSQL游标如何检查是否已经声明并因此释放的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！