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.
- When should you recommend using explicit transactions?
- When should you recommend using a different isolation level?
- What type of error handling should you recommend?
- What plans should you include for refactoring dynamic SQL?
Answers
- 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.
- 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.
- 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.
- 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.