SQL 服务器 - 游标错误

Question

我声明了一个动态游标，因为存储过程的嵌套基于数据的父子关系，这些数据可以进入多个级别，并且在处理时每天都在变化。 存储过程适用于父调用，但是此过程对子数据的过程会导致一条消息，指出"游标未打开"。 此消息出现在 Fetch 上，该消息在检查以确保光标已打开后立即出现。

DECLARE @OutCur CURSOR; 
DECLARE @curName as NVARCHAR(MAX);
...
SET @curName = LEFT(replace(replace(CONVERT (time, GETDATE()),':',''),'.',''),8);
SET @sqlCommand = 'SELECT a.myfields FROM mytable a;            
SET @sqlCommand = 'SET @cursor' + @curName + ' = CURSOR LOCAL FAST_FORWARD FOR ' + @sqlCommand + ' OPEN @cursor' + @curName + ';'
SET @curName = '@cursor' + @curName + ' cursor output';
EXEC sys.sp_executesql @sqlCommand,@curName,@OutCur output
IF CURSOR_STATUS('global','@OutCur')=-1
    OPEN @OutCur;
FETCH NEXT FROM @OutCur INTO @name,@type

提前感谢您的输入。

Answer 1

如果你取消注释关闭+解除分配 - 脚本工作正常：

GO
DECLARE @OutCur CURSOR; 
DECLARE @curName as NVARCHAR(MAX), @sqlCommand nvarchar(max), @name varchar(100), @type varchar(100)
declare @i int = 0
while @i < 5
begin
  SET @curName = LEFT(replace(replace(CONVERT (time, GETDATE()),':',''),'.',''),8);
  SET @sqlCommand = 'SELECT ''111'' as name, ''cc'' as type; '
  SET @sqlCommand = 'SET @cursor' + @curName + ' = CURSOR LOCAL FAST_FORWARD FOR ' + @sqlCommand + ' OPEN @cursor' + @curName + ';'
  SET @curName = '@cursor' + @curName + ' cursor output';
  EXEC sys.sp_executesql @sqlCommand,@curName,@OutCur output
  FETCH NEXT FROM @OutCur INTO @name,@type
  select @name, @type
  --close @OutCur
  --deallocate @OutCur
  set @i += 1
end
GO