TSQL does not call an infinite loop
See the code below:
declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20))
DECLARE @potentialresults TABLE (crime_urn varchar(20))
insert into @finalresults values ('1')
DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor
FETCH NEXT FROM finalresults_cursor INTO @crimeurn
WHILE @@FETCH_STATUS = 0
BEGIN
print @crimeurn
INSERT INTO @finalresults
values ('2')
FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END
select * from @finalresults --line 16
CLOSE finalresults_cursor
DEALLOCATE finalresults_cursor
Line 16 displays 5137 or 12342 rows in the SQL studio manager (this is a random difference). I expected TSQL to call an infinite loop because there is an insert into the table variable on every cursor iteration.
Why doesn't this cause an infinite loop? that is why there are 5,137 or 12,342 lines.
source to share
You are sticking to the heap.
The heap is disordered. There is no guarantee that the row will be inserted after the current row and will be retrieved on the next checkout.
I made a small change to the test framework and added an IDENTITY column. In my case, it got to 592.353 before exiting.
As you can see from the results below, this final line was inserted at an earlier page in the file (1623 to 184), so an ordered scan of the distribution starting at the penultimate line would not have found it.
Code to reproduce.
declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20), ID int identity)
DECLARE @potentialresults TABLE (crime_urn varchar(20))
insert into @finalresults values ('1')
DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor
FETCH NEXT FROM finalresults_cursor INTO @crimeurn
WHILE @@FETCH_STATUS = 0
BEGIN
print @crimeurn
INSERT INTO @finalresults
--OUTPUT INSERTED.ID
values ('2')
FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END
select *, sys.fn_PhysLocFormatter(%%physloc%%) from @finalresults --line 16
ORDER BY ID
CLOSE finalresults_cursor
DEALLOCATE finalresults_cursor
source to share
Edit: The information below is not correct, but I left it because I assume it should work.
By default, cursors do not work in INSENSITIVE
or mode STATIC
. By default, cursors DYNAMIC
and OPTIMISTIC
. The documentation on cursors does not mention how dynamic cursors work in relation to INSERTS
. The behavior INSERT
looks undocumented.
You may be able to fix this with an option SCROLL_LOCKS
to ensure that the order is saved.
Since the cursor definition is fixed at startup
DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
It is static after this point. Updating a table variable @finalresults
does not change the cursor finalresults_cursor
.
This is true:
X = 10 Y = X X = 20 PRINT X, Y
Outputs the following:
20 10
source to share
However, if you don't like or don't know the cursor type, you can use @@CURSOR_ROWS
inside your loop to do some "cursor" logic :).
There is some documentation here about the possible values a variable can have @@CURSOR_ROWS
, depending on the cursor type :.
source to share