c# - Is using @@TRANCOUNT useful? -

i have simple sp either insert or update depending on existence or non-existence of data in table.

create procedure [dbo].spupsert      -- parameters update / insert studentset     @studentsetid nvarchar(128),     @status_id int  begin     begin try         begin transaction             set xact_abort on;             set nocount on;              if exists(select studentsetid studentset studentsetid = @studentsetid)                 begin                     update studentset set modifieddate = getdate(), status_id = @status_id                     studentsetid = @studentsetid;                 end             else                 begin                      insert studentset                                 (studentsetid, status_id)                      values                            (                                 @studentsetid,                                 @status_id                            )                 end         commit transaction     end try      begin catch         rollback transaction     end catch  end 

wrote method so:

public void upsert(string studentsetid, int statusid) {     this.databasejobs.executesqlcommand(@"exec spupsert                                       @studentsetid = {0},                                       @status_id = {10} ",                                         studentsetid,                                         statusid); } 

here's how used: student has file, xml precise, sent processor calls sp part of process. multiple files can uploaded , processor designed work 5 files spawning 5 threads.

for batch of 5 files throws error:

transaction count after execute indicates mismatching number of begin , commit statements. previous count = 1, current count = 0. transaction count after execute indicates mismatching number of begin , commit statements. previous count = 1, current count = 0.

the number 5 not perfect one, may happen when more 5 files uploaded. lesser haven't tried.

so searched , found solution implements usage of @@trancount detailed here & here

@@trancount global variable , it's usage suggested in articles seem's it's local session. mean process in sql server can increase @trancount , relying on may not produce expected result.

my question what's way handle type of situation?

thanks in advance.

first, @@trancount informational - tells how many nested transactions in progress in current thread. in case, transaction in progress when stored procedure called hence transaction count 1.

your problem rollback rolls all transactions, including nested transactions. if wish abort whole batch, want, , error telling has happened.

however if want roll transaction created locally, must different. have save transaction right @ start, on error can roll point (before work done), , commit (with no work done).

begin tran declare @savepoint varbinary(16) set @savepoint = newid() save tran @savepoint begin try     -- stuff here     select 1/0; -- divide 0 error     commit tran end try begin catch     rollback tran @savepoint;     commit tran -- important!!!     --re-raise error if want (or recover in other way)      raiserror('rethrowing error', error_severity(), error_state()    ); end catch