sql - user defined type variable is not clearing the value inside the transaction -
i have scenario use temporary user defined type variable in transaction, temporary variable not clearing value inside transaction.
create type [int_list_table] table([item_id] [int] null) go declare @int_val bigint -- create temp table 5 rows ;with temp(int_val) (select 1 union select int_val = 1 + int_val temp int_val < 5 )select * #int temp; set nocount on declare intcursor cursor fast_forward select int_val #int open intcursor fetch next intcursor @int_val while(@@fetch_status = 0) begin begin try begin transaction -- temprory table store integer value declare @table [int_list_table] insert @table select 1 2 = @int_val -- @table should have resultset @ @int_val = 2, once filled values 3 , 4 select 'user table type value' = item_id, 'loop integer value' = @int_val @table commit transaction end try begin catch rollback end catch fetch next intcursor @int_val end close intcursor deallocate intcursor set nocount off
for above query output
actually has return record 2, not clearing value once initialized. please guide me proceed further.
the declaration of data type not clear content of table. once table variable there have whatever have put it. execution of code not have pass place variable declared. work well.
if 0 = 1 begin declare @table [int_list_table] end
you have make sure declaration before references variable.
to fix code want can of course add delete @table
before insert statement.
Comments
Post a Comment