Sample Header Ad - 728x90

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