Error handling - stored procedure nested in another stored procedure
0
votes
1
answer
918
views
I've got a stored procedure that call another stored procedure.
And i sometime get an error :
System.Data.SqlClient.SqlError: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
At the advise of my teammate I plan to implement an error handling in the Try Catch that deals with this problem for both the stored procedure.
Here is the Catch:
BEGIN CATCH
IF @@TRANCOUNT = 1 -- if this is the last tranasction - roll it back and close the transaction
ROLLBACK;
IF @@TRANCOUNT > 1 -- if this is not the last tranasction - commit it and return to the previous transaction
COMMIT;
THROW; -- in any case, throw the original error
END CATCH
i understand it deals with transaction that isn't closed if it's a nested procedure
that way it can rollback and commit.
and i try to understand what happens in each case:
1. Main procedure running failed before running the nested procedure -
2. Main procedure running failed after running the nested procedure that succeeded -
3. Main procedure running and there is a failure in the nested procedure -
here what happens :
1. catch on
IF @@TRANCOUNT = 1 -- if this is the last tranasction - roll it back and close the transaction
ROLLBACK;
and rollback
2. what happens for this case?
is it this case IF @@@@TRANCOUNT > 1 and if so what happens for the already nested transaction that was commited?
3. what happens for this case?
it this case IF @@@@TRANCOUNT > 1 and all the transactions are rollbacked.
is my logic for 2 and 3 correct?
and is this the right error handling for nested transactions in stored procedure?
Asked by dexon
(65 rep)
Jan 30, 2024, 01:01 PM
Last activity: Jan 30, 2024, 06:26 PM
Last activity: Jan 30, 2024, 06:26 PM