sql - What is the right way to use multiple savepoints -

i'm using transactions first time, might asking silly question.

i want insert data in 3 tables:

table1(p1,p2,p3) table2(q1,q2) table3(t3,fk1,fk2) 

for example, if goes wrong , data cannot inserted table2, data table1 not lost , table3 remains unchanged (and viceversa).

i've tried 2 versions far, none of them satisfying.

version 1:

create procedure insertinto(@p1,@p2,@p3,@q1,@q2,@t3)) begin  begin tran set xact_abort off  save transaction point1 begin try     declare @fk1 int     insert table1 values (@p1,@p2,@p3)     select @fk1 = table1.id table1 table1.p1 = @p1      save transaction point2     begin try         declare @fk2 int         insert table2 values (@q1,@q2)         select @fk2 = table2.id table2 table2.q1 = @q1          save transaction point3         begin try             insert table3 values (@t3, @fk1, @fk2)             commit tran             end try         begin catch             rollback transaction point3             commit tran         end catch      end try     begin catch         rollback transaction point2         commit tran     end catch  end try begin catch     rollback transaction point1     commit tran end catch end 

but if data cannot inserted in table1, possible data table2 lost , don't want lose anything. so, i've tried split it.

version 2:

create procedure insertinto(@p1,@p2,@p3,@q1,@q2,@t3) begin  begin tran set xact_abort off  save transaction point1 begin try     declare @fk1 int     insert table1 values (@p1,@p2,@p3)     select @fk1 = table1.id table1 table1.p1 = @p1 end try begin catch     rollback transaction point1     commit tran end catch  save transaction point2 begin try     declare @fk2 int     insert table2 values (@q1,@q2)     select @fk2 = table2.id table2 table2.q1 = @q1 end try begin catch     rollback transaction point2     commit tran end catch  save transaction point3 begin try     insert table3 values (@t3,@fk1,@fk2)     commit tran  end try begin catch     rollback transaction point3     commit tran  end catch end 

but if insert table2 fails, this:

(1 row(s) affected)

(0 row(s) affected)
msg 628, level 16, state 0, procedure insertinto, line 26 (second begin catch)
cannot issue save transaction when there no active transaction.

how can right?

save tran requires transaction count > 0, must have committed transaction in previous catch block. have several options:

1) replace save tran statement following (you can use same savepoint name, rollback rollback last savepoint):

if @@trancount = 0     begin tran; else     save tran tran1; 

2) in catch block, add begin tran after commit tran

begin catch     rollback transaction point1     commit tran     begin tran end catch 

3) remove commit tran inside catch blocks, , single commit @ end.