CODEWITHSUNDEEP
t-sqltemplatestransactions
AnotherDeveloper
AnotherDeveloper

Reputation: 1272

Look for best practice when handling errors in TSQL Scripts

I'm trying to come up with a template for error handling within a stored procedure or just general statements that would be able to handle 99% of failures or errors.

After looking online for different examples of SQL transactions and try-catches I came up with this template that I wanted to share with some coworkers Wanted to see if anyone thought I was missing something.

BEGIN TRY
   BEGIN TRAN
      --Place code in here. 



      --Your Code Ends Here
   COMMIT
END TRY
BEGIN CATCH
   -- There was an error
   IF @@TRANCOUNT > 0
   ROLLBACK --Rolls back from where error first discovered. All code after error not run.

   -- Raise an error with the details of the exception
   DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

   SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
   RAISERROR(@ErrMsg, @ErrSeverity, 1) --This will be picked up by .NET Exception handler.
END CATCH

Thanks!

Upvotes: 1

Views: 89

Answers (1)

HLGEM
HLGEM

Reputation: 96600

Depending on what the proc does, it is sometimes good to store more data about the error in a logging table. For instance you might want to store not only the error message but the parameters that were sent into the proc so that you you can troubleshoot knowing exactly what data casued the issue. If you have many steps in a proc as we often do for complex inserts, you might want to store information about what step you were on when the error occurred as some error messages can be less than helpful when faced with a 1000 line proc. The way I do this is to set up a table variable at the beginning that contains the fields I will want to store in my logging table. Then inset data to it as I go through steps. I may choose to store all the steps in the table variable or repalce each step with the data for the new one just before it is run. Then when you hit the catch block update the table varaible with the error message data. Table variables are still available after you have rolled back the transaction, so you can use that table variable after the rollback to insert into your logging table. This is especially helpful to diagnose the problem when a proc only rarely fails.

Upvotes: 1

Related Questions