Table variable declared within cursor persists across loop iterations

Question

Table variable declared within cursor persists across loop iterations

SSCrazy

Points: 2157

May 6, 2024 at 8:33 pm

#4419125

If you run this code it creates a 2 row cursor loop. Within the cursor the temp table @spec is declared and inserted into (as the output of a select). What was unexpected for me is in the second iteration of the loop the temp table contains 2 rows. The "solution" to this issue I'm working with currently is to delete the temp table rows prior to fetching the next row, i.e. the commented out 'delete @spec;'. Is this the expected behavior? It wasn't my expectation and it took a while to isolate

drop table if exists #test;
go
create table #test(
  t_id                      bigint identity(1,1) primary key not null,
  a_id                      bigint not null,
  a_name                    nvarchar(10) not null);
go

declare web_cursor cursor local forward_only static read_only for
select a.a_id, a.a_name
from (values (1, 'xyz'), (2, 'abc')) a(a_id, a_name);

open web_cursor

declare 
    @csr_a_id               bigint,
    @csr_a_name             nvarchar(10)

fetch next from web_cursor into @csr_a_id, @csr_a_name

while @@fetch_status = 0
begin
    declare @spec                           table(t_id            bigint not null primary key);

    insert #test(a_id, a_name)
    output inserted.t_id into @spec
    select @csr_a_id, @csr_a_name;

    /* print out the output in JSON to see row(s) */    
    declare @j nvarchar(max)=(select * from @spec for json path);
    print (@j);

    --delete @spec;
    fetch next from web_cursor into @csr_a_id, @csr_a_name
end

close web_cursor
deallocate web_cursor

This topic was modified 6 months, 3 weeks ago by Steve Collins. Reason: Added temp table definition

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Interesting.

It would appear that the second and subsequent executions of declare @spec are completely ignored.

As there is no way (as far as I know) of destroying or amending a table variable after it has been declared (within the same batch), this is fairly sensible behaviour. The only practical alternative would be to throw an error.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Thanks Phil. Interesting maybe. The real answer is don't use cursors 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

or define variable outside cursor and delete it in each loop - only feasible if the table variable does not have a identity column which needs reseed - see https://www.grapefruitmoon.net/table-variables-identity-columns-and-reseeding/

but if a cursor is indeed required then a #temp table would be the best option as you can then truncate it which will reset the identity if ever used.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Steve Collins wrote:

Thanks Phil. Interesting maybe. The real answer is don't use cursors 🙂

It's nothing to do with the cursor, it's how table variables work. Despite being declared within a block, they behave more like temporary tables than local variables when it comes to their scope and lifespan. Table variables are cleaned up when the script or batch that declared them finishes execution, not at the end of a code block. This means that within a loop, unless explicitly cleared (like using DELETE FROM @spec;), they retain their data across iterations of the loop.