venkataramanaiahsr
asked on
executing nested stored procedures within the transaction
I have two sp - sp1 and sp2
Sp1 is called from the client program
SP1 code
Begin Try
Begin Transaction
1....
2....
if @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
Begin
ROLLBACK
Select 'Mismatch in AmtRefund_Receipt and
AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
ErrorCondition
End
exec sp2
commit transaction
End Try
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK
Select Error_message() ErrorCondition
END CATCH
sp2 code ( not enclosed within begin and commit transaction and try catch block)
my problem is that if the condition is not met only sp1 is getting rolled back and sp2 is getting executed.
In one of previous post i was told that once the sp2 is executed with in the Begin transaction of the sp1 , sp2 is implicitely part of sp1 transaction.
can some expert help me to resolve the issue
Sp1 is called from the client program
SP1 code
Begin Try
Begin Transaction
1....
2....
if @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
Begin
ROLLBACK
Select 'Mismatch in AmtRefund_Receipt and
AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
ErrorCondition
End
exec sp2
commit transaction
End Try
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK
Select Error_message() ErrorCondition
END CATCH
sp2 code ( not enclosed within begin and commit transaction and try catch block)
my problem is that if the condition is not met only sp1 is getting rolled back and sp2 is getting executed.
In one of previous post i was told that once the sp2 is executed with in the Begin transaction of the sp1 , sp2 is implicitely part of sp1 transaction.
can some expert help me to resolve the issue
Last Comment
ASKER
I have done exactly the same. it is serving the purpose. but my question is what happens any error occurs in sp2. i have not enclosed any try catch block or begin commit transaction in sp2. If sp2 is part of the transaction inititated by sp1 how come it is getting executed when sp1 is getting rolled back
if @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
Begin
ROLLBACK
Select 'Mismatch in AmtRefund_Receipt and
AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
ErrorCondition
End
exec sp2
exec sp2 is an open statement outside of any if statements
a rollback says undo my transactions do thus far.
eg:
begin transaction
update tbl set col = 1 -- was 0, no one else can update until I'm done and still sees 0
rollback transaction -- col now = 0 again
the rest of your code will still fire even during a rollback. I believe your rollback is happening due ot your if statement, rather than any error. So after the if statement is processed the next statement is to fire SP2. I got around it with the else statement.
Begin
ROLLBACK
Select 'Mismatch in AmtRefund_Receipt and
AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
ErrorCondition
End
exec sp2
exec sp2 is an open statement outside of any if statements
a rollback says undo my transactions do thus far.
eg:
begin transaction
update tbl set col = 1 -- was 0, no one else can update until I'm done and still sees 0
rollback transaction -- col now = 0 again
the rest of your code will still fire even during a rollback. I believe your rollback is happening due ot your if statement, rather than any error. So after the if statement is processed the next statement is to fire SP2. I got around it with the else statement.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
sorry for the delay in accepting the answer
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
If so add an else:
if @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
Begin
ROLLBACK
Select 'Mismatch in AmtRefund_Receipt and
AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
ErrorCondition
End
ELSE
exec sp2