I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?
In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.
To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.
To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.
I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.
I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.
Thank
Why don't you add a column to the legacy output to indicate errors. That way nothing is stopped, but you can determine at what point errors start and output to the user, or react accordingly.
|||Thank you for your response but the problem with that is I want things to stop, I dont want the data in both tables to be out of sync. The only reason we are supporting the older table is the web application is quite huge, very very large and at this point, rewriting legacy code (using classic ASP) to use our new tables would be time consuming. Additionaly, we have several apps wrote in Visual Basic 6 that would need to be rewrote as well to support our newer system. I am developing a new Payment Processing System in C# that writes to the database, and caputres additional data that is useful instead of waiting for end of month. The old system is very very inefficient but until we have the resources to go back and recode it, End of Month Reports still run off of it. These jobs take 2 hours to run (Yeah lots of data) However, the purpose of the new system is to capture some of that data that wasn't avaialble until the end of month. It does this by writing the data that one department needs concering payments and adjustments at the time that action is performed.
However, we cannot have any one table having a different value than the other. If I do it the way you stated then if there is an error, our historical end of month data will be different than data in our new table.
From my understanding, if I wrap everything in a TRY CATCH block in sql server and if any one statment or procedure fails and I am raising the error, the Outermost TRY CATCH BLOCK will also fail, thus braching off to its CATCH BLOCK and inside that CATCH Block I have a rollback. From what I have read, the outermost COMMIT and ROLLBACK are what matters and if it ROLLSBACK then everything rolls back.. I just want to make sure I am understanding this correctly.
Thanks!
John
|||
jgilmore:
Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?
Yes. In fact, that's the default behavior of "nested transactions"
Seehttp://www.codeproject.com/KB/database/sqlservertransactions.aspx and scroll down to "nested transactions" but here's the nutshell answer. Assuming proc1 executes proc2, which contains a begin tran/commit, as long as proc1 wraps the call to proc2 in another begin tran/commit/rollback, a rollback done as part of the outer transaction will in fact roll back the transaction done by proc2, even if proc2's transaction has commited.
For many more articles, just google "sql server nested transaction"
|||Thank you dbland07666!
I wanted to ensure that this worked as I expected it to.
John
No comments:
Post a Comment