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)
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.