@@FETCH_STATUS lives outside the cursor

Question

I have a query which processes XML data and I use a while(@@FETCH_STATUS = 0) loop for data returned from the cursor.

When I run the query using Management Studio, @@FETCH_STATUS equals -1 and the code inside my loop is omitted. If I run the query using the debugger and press continue, it runs just fine and the @@FETCH_STATUS equals 0. When I run the query again, after running it in debug @@FETCH_STATUS equals 0 and changes to -1.

To sum up:

1. I run with SSMS - @@FETCH_STATUS = -1
2. I run with debugger - @@FETCH_STATUS = 0 (I want this value)
3. I run with SSMS once after running with debugger @@FETCH_STATUS still equals 0 but then changes to -1.

I use OPEN cursor, CLOSE cursor and DEALLOCATE cursor. Why does it work this way?

EDIT: Code you asked for:

IF (OBJECT_ID('dbo.XmlOrderResponses') IS NOT NULL)
    DROP TABLE XmlOrderResponses;

CREATE TABLE XmlOrderResponses (
    OrderResponseType INT
    ,OrderResponseNumber NVARCHAR(40)
    ,OrderResponseDate DATETIME
    ,DocumentFunctionCode NVARCHAR(40)
    ,Remarks INT
    );

DECLARE CUR CURSOR
FOR
SELECT Subdirectory
FROM XMLFiles;

OPEN CUR

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @DocHandle AS INT;
    DECLARE @TMP AS NVARCHAR(512);

    FETCH NEXT
    FROM Cur
    INTO @TMP

    DECLARE @XmlDocument AS NVARCHAR(MAX);

    SET @XmlDocument = (
            SELECT CAST(XMLSource AS NVARCHAR(max))
            FROM XMLFiles
            WHERE subdirectory = @TMP
            );

    EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT
        ,@XmlDocument;

    INSERT INTO XmlOrderResponses (
        OrderResponseType
        ,OrderResponseNumber
        ,OrderResponseDate
        ,DocumentFunctionCode
        ,Remarks
    )
SELECT *
FROM OPENXML(@DocHandle, '/Document-OrderResponse/*', 11) WITH (
        OrderResponseType INT
        ,OrderResponseNumber NVARCHAR(40)
        ,OrderResponseDate DATETIME
        ,DocumentFunctionCode NVARCHAR(40)
        ,Remarks INT
        );

    EXEC sys.sp_xml_removedocument @DocHandle;
END

CLOSE CUR;

DEALLOCATE CUR;

--I know I shouldn't be doing that but I can't get rid of NULL records the other way.
DELETE
FROM XmlOrderResponses
WHERE OrderResponseType IS NULL
    AND OrderResponseNumber IS NULL
    AND OrderResponseDate IS NULL
    AND DocumentFunctionCode IS NULL
    AND Remarks IS NULL;

SELECT *
FROM XmlOrderResponses

SELECT @@FETCH_STATUS

Answer 1

The problem is that the first time you refer to @@FETCH_STATUS, you have not done a fetch with your cursor, so it is referring to the last cursor used. Imagine this simple example:

DECLARE C1 CURSOR
FOR
    SELECT TOP 3 ID
    FROM (VALUES ('1'), ('2'), ('3')) t (ID);

OPEN C1;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @c1 CHAR(1);
    FETCH NEXT FROM C1 INTO @c1;
    PRINT @c1;
END

CLOSE C1;
DEALLOCATE C1;

DECLARE C2 CURSOR 
FOR 
    SELECT TOP 3 ID
    FROM (VALUES ('1'), ('2'), ('3')) t (ID);

OPEN C2;

-- HERE @@FETCH_STATUS REFERS TO THE LAST FETCH FOR CURSOR `C1` NOT `C2`
SELECT @@FETCH_STATUS;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @c2 CHAR(1);
    FETCH NEXT FROM C2 INTO @c2;
    PRINT @c2;
END;

CLOSE C2;
DEALLOCATE C2;

At the commented line, even though you have closed, and deallocated C1, @@FETCH_STATUS is still referring to this cursor (since no other FETCH has been performed since), so you never enter your loop for C2

You should perform the Fetch before the loop, then at the end of each loop, rather than at the beginning.

DECLARE @TMP AS NVARCHAR(512);
OPEN CUR
-- DO FETCH FIRST
FETCH NEXT FROM Cur INTO @TMP

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @DocHandle AS INT;

    -- DO ALL YOUR WORK WITH @TMP


    --PERFORM THE FETCH AGAIN AT THE END OF THE LOOP
    FETCH NEXT FROM Cur INTO @TMP
END

The other problem you have with doing FETCH at the start of each loop, is that the last item will be processed twice. Again a simple example (and assuming you enter the loop with @@FETCH_STATUS = 0)

DECLARE C1 CURSOR 
FOR
    SELECT ID = '1';

OPEN C1;
DECLARE @c CHAR(1);
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @c1 CHAR(1);
    FETCH NEXT FROM C1 INTO @c1;
    PRINT @c1;
END

This will print

1
1

Because, when @@FETCH_STATUS is -1, FETCH will just return the item at the current position.