Implementing Error Handling :: SQL Server 2012

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions. You need to put a plan together to justify your activity.
  1. When should you recommend using explicit transactions?

  2. When should you recommend using a different isolation level?

  3. What type of error handling should you recommend?

  4. What plans should you include for refactoring dynamic SQL?
  1. Whenever more than one data change occurs in a stored procedure, and it is important that the data changes be treated as a logical unit of work, you should add transaction logic to the stored procedure.

  2. You need to adapt the isolation levels to the requirements for transactional consistency. You should investigate the current application and the database for instances of blocking and especially deadlocking. If you find deadlocks, and establish that they are not due to mistakes in T-SQL coding, you can use various methods of lowering the isolation level in order to make deadlocks less likely. However, be aware that some transactions may require higher levels of isolation.

  3. You should use TRY/CATCH blocks in every stored procedure where errors might occur, and encourage your team to standardize on that usage. By funneling all errors to the CATCH block, you can handle errors in just one place in the code.

  4. Check the stored procedures for the use of dynamic SQL, and where possible, replace calls to the EXECUTE command with the sp_executesql stored procedure.