>I am using nested Transaction and also using recursive relation of Primary Key and Foreign Key in one table. I am also calling stored procedure (with transaction) within a transaction. is that ok?
>
>

>
>    begin transaction t_update_deposit_advance	
>
>  /*
>   Some table insertion and temporary tables.
>  */
>
>    /* this line  also wrap with Transaction
>     */
>     EXEC spUpdateBalofBillPayable @PkTranIDTranWithBal
>
>    /* this line  also wrap with Transaction
>     */
>     EXEC spUpdateTransBalanceFromReceivePayment @PkTranIDTranWithBal
>
>
>
> IF  (
>      (@error_var1 <> 0) or
>      (@error_var2 <> 0)
>  )
>  begin
>	set @HasError=1
>
>	ROLLBACK TRANSACTION t_update_deposit_advance	
>
>
>
>  end
>else
>  begin
>
>	set @HasError=0
>	COMMIT TRANSACTION t_update_deposit_advance	
>  end	
>	
>


Simplicio,

Nested transactions are okay.  Have you analyzed the execution plans for the queries?