Table Variable inside cursor, strange behaviour - SQL Server

Question

I observed a strange thing inside a stored procedure with select on table variables. It always returns the value (on subsequent iterations) that was fetched in the first iteration of cursor. Here is some sample code that proves this.

DECLARE @id AS INT;
DECLARE @outid AS INT;

DECLARE sub_cursor CURSOR FAST_FORWARD
    FOR SELECT [TestColumn]
        FROM   testtable1;

OPEN sub_cursor;

FETCH NEXT FROM sub_cursor INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN            
        DECLARE @Log TABLE (LogId BIGINT NOT NULL);
        PRINT 'id: ' + CONVERT (VARCHAR (10), @id);

        INSERT INTO Testtable2 (TestColumn)
        OUTPUT inserted.[TestColumn] INTO @Log
        VALUES (@id);

        IF @@ERROR = 0
        BEGIN
                SELECT TOP 1 @outid = LogId
                FROM   @Log;
                PRINT 'Outid: ' + CONVERT (VARCHAR (10), @outid);

                INSERT INTO [dbo].[TestTable3] ([TestColumn])
                VALUES (@outid);
        END
        FETCH NEXT FROM sub_cursor INTO @id;
    END

CLOSE sub_cursor;

DEALLOCATE sub_cursor;

However, while I was posting the code on SO and tried various combinations, I observed that removing top from the below line, gives me the right values out of table variable inside a cursor.

SELECT TOP 1 @outid = LogId FROM   @Log;

which would make it like this

SELECT @outid = LogId FROM   @Log;

I am not sure what is happening here. I thought TOP 1 on table variable should work, thinking that a new table is created on every iteration of the loop. Can someone throw light on the table variable scoping and lifetime.

Update: I have the solution to circumvent the strange behavior here. As a solution, I have declared the table at the top before the loop and deleting all rows at the beginning of the loop.

Answer 1

There are numerous things a bit off with this code.

First off, you roll back your embedded transaction on error, but I never see you commit it on success. As written, this will leak a transaction, which could cause major issues for you in the following code.

What might be confusing you about the @Log table situation is that SQL Server doesn't use the same variable scoping and lifetime rules as C++ or other standard programming languages. Even when declaring your table variable in the cursor block you will only get a single @Log table which then lives for the remainder of the batch, and which gets multiple rows inserted into it.

As a result, your use of TOP 1 is not really meaningful, since there's no ORDER BY clause to impose any sort of deterministic ordering on the table. Without that, you get whatever order SQL Server sees fit to give you, which in this case appears to be the insertion order, giving you the first inserted element of that log table every time you run the SELECT.

If you truly want only the last ID value, you will need to provide some real ordering criterion for your @Log table -- some form of autonumber or date field alongside the data column that can be used to provide the proper ordering for what you want to do.