What does the -9 value for fetch_status mean in SQL Server?

Question

I am trying to get fetch_status for each cursor in a nested loop. I cannot use @@FETCH_STATUS here due to nested loops (loop in another loop)

My first approach is to use [sys.dm_exec_cursors][1] to determine the status of each cursor by name. Then, if fetch_status is not 0, I would break the loop.

According to doc, fetch_status should return one of the following values: 0, -1, -2

However, fetch_status in my case returns -9 . What does -9 mean?

This is how I try to get cursor status based on name

 SET @fetchStatus = (SELECT TOP 1 [fetch_status] FROM sys.dm_exec_cursors (@@SPID | 0 ) WHERE name = 'pageCursor'); 

Here is a short version of my stored procedure

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[CloneSurvey] @sourceSurveyId INT, @newSurveyName VARCHAR(255) AS BEGIN DECLARE @fetchStatus INT; If CURSOR_STATUS('global','pageCursor') <> -3 DEALLOCATE pageCursor; DECLARE pageCursor CURSOR FOR SELECT [id] FROM [survey_pages] WHERE survey_id = @sourceSurveyId; DECLARE @lastPageId INT; DECLARE @pageId INT; SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY BEGIN TRAN Survey; OPEN pageCursor; WHILE 1 = 1 BEGIN SET @fetchStatus = (SELECT TOP 1 [fetch_status] FROM sys.dm_exec_cursors (@@SPID | 0 ) WHERE name = 'pageCursor'); SELECT 'FETCH STAUS => ' + CONVERT(VARCHAR, @fetchStatus); if @fetchStatus <> 0 AND @fetchStatus <> -9 BREAK; FETCH NEXT FROM pageCursor INTO @pageId INSERT INTO surveys(page_id) SELECT @pageId; @lastPageId = SCOPE_IDENTITY(); EXEC [dbo].[CloneQuestion] @lastPageId END; CLOSE pageCursor; DEALLOCATE pageCursor; COMMIT TRAN Survey; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; IF(@@TRANCOUNT > 0) ROLLBACK TRAN Survey; END CATCH; END 

Answer 1

According to the documentation, the fetch_status column stores the last returned @@FETCH_STATUS . This means that fetch_status is undefined before the first FETCH fetch_status against the cursor, and it seems that -9 is used to encode this. The example below really shows (SQL Server 2012) that the value -9 before the first FETCH is called:

 DECLARE cur1 CURSOR LOCAL FOR SELECT 1 OPEN cur1 --fetch_status = -9 SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'cur1' FETCH NEXT FROM cur1; --fetch_status=0 SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'cur1'