TSQL Try / Catch within Transaction or vice versa?

by

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

More Related Contents:

  1. SQL Server: Isolation level leaks across pooled connections
  2. How do I create a unique constraint that also allows nulls?
  3. How do I get list of all tables in a database using TSQL?
  4. Get first day of week in SQL Server
  5. What is the significance of 1/1/1753 in SQL Server?
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. Escape a string in SQL Server so that it is safe to use in LIKE expression
  8. Insert Update trigger how to determine if insert or update
  9. SQL time difference between two dates result in hh:mm:ss
  10. What is the best way to truncate a date in SQL Server?
  11. Dynamic SQL (passing table name as parameter)
  12. SQL Server: Difference between PARTITION BY and GROUP BY
  13. How do you truncate all tables in a database using TSQL?
  14. Do I really need to use “SET XACT_ABORT ON”?
  15. Understanding SQL Server LOCKS on SELECT queries
  16. TSQL: How to convert local time to UTC? (SQL Server 2008)
  17. T-SQL Dynamic SQL and Temp Tables
  18. TSQL Email Validation (without regex)
  19. Dynamic SQL results into temp table in SQL Stored procedure
  20. SQL Server Output Clause into a scalar variable
  21. TABLOCK vs TABLOCKX
  22. How to convert number of minutes to hh:mm format in TSQL?
  23. What happens if you don’t commit a transaction to a database (say, SQL Server)?
  24. How do I select last 5 rows in a table without sorting?
  25. SELECT INTO a table variable in T-SQL
  26. T-SQL query to show table definition?
  27. How can I insert binary file data into a binary SQL field using a simple insert statement?
  28. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  29. Include missing months in Group By query
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment