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.

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

Popular posts from this blog

jasper reports - Fixed header in Excel using JasperReports -

media player - Android: mediaplayer went away with unhandled events -

python - ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000') or TypeError: 'tuple' object is not callable -