Print vs select output order in Visual Studio (was: Temporary SQL table changes on it's own???)

Please tell me I'm dreaming or something.

I'm doing oldschool tsql tree traversal without CTE. Here's my vanilla stack table.

CREATE TABLE #stack (DepartmentId int, level int)

Later in the loop code I've ran into a problem, so I've sprinkled the code with Print statements and SELECT * FROM #stack. Here's the relevant part for this question

IF EXISTS (SELECT * FROM #stack WHERE level = @level)
        BEGIN
            PRINT 'First in the loop:'
            SELECT * FROM #stack WHERE level = @level
            PRINT 'Same query 1:'
            SELECT * FROM #stack WHERE level = @level

This renders this output:

First in the loop:
DepartmentId                                                                                                                                                                                                                                                     level                                                                                                                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2                                                                                                                                                                                                                                                                1                                                                                                                                                                                                                                                                
No rows affected.
(1 row(s) returned)
Same query 1:
DepartmentId                                                                                                                                                                                                                                                     level                                                                                                                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
NULL                                                                                                                                                                                                                                                             1                                                                                                                                                                                                                                                                
No rows affected.
(1 row(s) returned)

My question is obviously, how can the content of the first column change between the two select statements?

UPDATE: The above output is from running the query from a database project in Visual Studio 2008. However, running the query from SQL Server Management Studio produces the following result:

First in the loop:
DepartmentId level
------------ -----------
NULL         1

Same query 1:
DepartmentId level
------------ -----------
NULL         1

My code is not far from the classic msdn article "Expanding Hierarchies", but I'm using it to fill a cache table with id's instead of building strings.

I'm also running it in a transaction like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON
BEGIN TRANSACTION

EDIT 2: It seems the loop is contributing to the confusion, but the main culprit is the order of the Print statements and the Select statements in Visual Studio.

Here's the minimized code that produces the relevant output:

PRINT 'First in the loop:'
SELECT 'The order '
PRINT 'Same query 1:'
SELECT 'is quite puzzling'
PRINT 'Same query 2:'
SELECT 'isn´t it?'

Here's what VS 2008 outputs:

Executing selected script from a_error_repro.sql

First in the loop:
Same query 1:
Column1    
---------- 
The order  
No rows affected.
(1 row(s) returned)
Same query 2:
Column1      
------------ 
is quite puzzling. 
No rows affected.
(1 row(s) returned)开发者_如何学C
Column1   
--------- 
isn´t it? 
No rows affected.
(1 row(s) returned)
Finished executing selected script from a_error_repro.sql

It seems regarding the content of the temporary tables, we're actually fine, but the output order of print statements in VS 2008 is a bit disturbing.

I've added a bug report as recommended by Martin. Thank you. :) https://connect.microsoft.com/VisualStudio/feedback/details/664137/wrong-output-order-of-print-vs-select-when-executing-sql-statements-in-visual-studio

The problem was not in the code nor in the tables. It was a problem with the output order of the print statements vs select statements in visual studio.

See: https://connect.microsoft.com/VisualStudio/feedback/details/664137/wrong-output-order-of-print-vs-select-when-executing-sql-statements-in-visual-studio

It might be that the data within your second select is out of scope (Change the single #-sign to 2 #-signs and see if you get the same result.). Since that seems strange with the code you showed, please add some more code.