Menu
I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing.
This is a summary of the script.
BEGIN TRANSACTION SCHEDULEDELETE BEGIN TRY DELETE -- delete commands full SQL cut out DELETE -- delete commands full SQL cut out DELETE -- delete commands full SQL cut out PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out. END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage ROLLBACK TRANSACTION SCHEDULEDELETE PRINT 'Error detected, all changes reversed.' END CATCH --COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct. --ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever. This is my first time writing transaction, is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?
The important factor in this script is that the user must manually commit the transaction.
768
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
If the stored procedure contains a TRY... CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.
Note that you cannot use TRY... CATCH blocks inside T-SQL UDFs. If you have to capture errors that occur inside a UDF, you can do that in the calling procedure or code.
XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error. A TRY...CATCH construct cannot span multiple batches. A TRY...CATCH construct cannot span multiple blocks of Transact-SQL statements.
I would recommend you to not use TRY/CATCH statements within transaction. The problem is that transaction becomes uncommitable if some error occurs inside your TRY statement (even if it's successfully processed by CATCH statement).
A TRY...CATCH construct cannot span multiple batches. A TRY...CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY...CATCH construct cannot span two BEGIN...END blocks of Transact-SQL statements and cannot span an IF...ELSE construct.
A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block.
Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.
If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required.
I have added a little check for any open transaction using @@TRANCOUNT function before actually rolling back the transaction. It doesn't really make much sense in this scenario. It is more useful when you are doing some validations checks in your TRY block before you open a transaction like checking param values and other stuff and raising error in the TRY block if any of the validation checks fail. In that case, the control will jump to the CATCH block without even opening a transaction. There you can check for any open transaction and rollback if there are any open ones. In your case, you really don't need to check for any open transaction as you will not enter the CATCH block unless something goes wrong inside your transaction.
Do not ask after you have executed the DELETE operation whether it needs to be committed or rolled back; do all these validation before opening the transaction. Once a transaction is opened, commit it straightaway and in case of any errors, do error handling (you are doing a good job by getting detailed info by using almost all of the error functions).
BEGIN TRY BEGIN TRANSACTION SCHEDULEDELETE DELETE -- delete commands full SQL cut out DELETE -- delete commands full SQL cut out DELETE -- delete commands full SQL cut out COMMIT TRANSACTION SCHEDULEDELETE PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out. END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION SCHEDULEDELETE PRINT 'Error detected, all changes reversed' END SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage END CATCH If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
