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

本文介绍了TSQL游标如何检查是否已经声明并因此释放的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 如果在尝试再次打开游标之前，如何确定已解除游标的存取状态？ 对于表格，我可以使用： / p> 如果存在（从tempdb.sys.tables中选择前1个，其中name ='## tmpTable'） drop table ## tmpTable; ...然后我可以重新创建我的## tmpTable 但我不能工作out如何做一个游标像 - 首先清理，如果已经存在.. ... ..< -----这是什么？ - 声明并使用游标 DECLARE someCursorName CURSOR FOR 从某处选择内容 FOR READ ONLY 我这样做是为了确保我的脚本在开始工作之前清理。 最好的我可以想出是： 开始尝试DEALLOCATE someCursorName;这是一个很好的做法吗？ $ 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 cursorDECLARE someCursorName CURSOR FOR select something from somewhere FOR READ ONLYI'm doing this to ensure that my script cleans up before it starts workBest I can come up with is :begin try DEALLOCATE someCursorName ; end try begin catch end catchIs 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 tablesfind 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_FORWARDFOR 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游标如何检查是否已经声明并因此释放的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！