Search code examples
sqlsql-servercursor

SQL Server - Cursor Error


I am declaring a dynamic cursor due to a nesting of the stored procedure based on a parent-child relationship of the data that can go multiple levels and vary daily when processed. The stored procedure works for the parent call, however the process of this procedure for child data causes a message stating that the "Cursor is not open." This message occurs on the Fetch which is immediately after checking to be sure the cursor is open.

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

Thanks in advance for the input.


Solution

  • if you uncomment close+deallocate - script works fine:

    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