Implement error handling

May include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @ERROR.

Unfortunately, we cannot write bullet-proof code in any language, T-SQL included. Fortunately, most languages provide ways to deal with errors within your applications, T-SQL included (at least, Microsoft’s implementation).

The easiest but most limited way to deal with errors is to query the @@ERROR global variable. If any error occurs, @@ERRROR returns its number. If not, it returns 0. The severe limitation is that it resets its value after each successful statement. And a simple SELECT @@ERROR counts as a successfully executed statement, so you won’t be able to pass the error information any further.

But I went too far. Let’s see what builds up an error in SQL Server:

  • Error number: a unique number which identifies the current error message. SQL Server reserves the error numbers 0-50000, so you can build custom errors from number 50001.
  • Severity level: from 0 to 25, indicates the severity of what has happened.
  • Error message: a textual representation of the error.

Continue reading

Advertisements

Manage transactions

May include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL

A transaction is a group of related tasks, which must succeed or fail as a unit. A transaction is referred as an ACID operation. ACID stands for Atomicity, Consistency, Isolation and Durability.

To start a transaction, simply write BEGIN TRANSACTION. You can optionally specify a transaction name for it. To end it successfully, use the COMMIT clause, to undo the changes, use the ROLLBACK clause, along with the optional name of the transaction. Anything between the BEGIN TRAN(SACTION) and the ending clause is considered to be part of it.

In SQL Server, we distinct some kinds of transactions. There are explicit transactions, which are explicitly marked with the BEGIN TRAN keyword. There are also implicit transactions, which can be used after calling the SET IMPLICIT_TRANSACTIONS ON statement. After this, any SQL statement will be executed as a transaction.

A nice feature to use is SET XACT_ABORT. By default, if a run-time statement error occurs in a SQL statement, only that statement is rolled back. If you specify SET XACT_ABORT, the whole batch will be rolled back instead of just the one causing the error.

Continue reading