3.2. Handling errors and transactions

With T-SQL like any programming language we can handle erros making use of TRY and CATCH blocks, the basic structure is:

BEGIN TRY
  /* some code here */
END TRY
BEGIN CATCH
  /* error handling */
END CATCH

As you can see the error handling in SQL Server are simple, we have a try catch block, in the try block we can write some code, if some error occurs in any code inside try block, the catch block are executed e in this block must have an error handle. Before explain more details about error handling let's see some thing about the errors in the SQL Server. Unfortunately the try-catch block aren't avaliable in user functions, this can be used only in procs and batchs.

Errors in the SQL Server

The erros in SQL Server have some properties like:

number number
message text
state number
severity number
procedure text

line number

With this itens we can get some informations about the error:

number can identify an error;
message describe the error;
state are normally used to specify where the error occur

severity is possible understand the criticality about an error:

Severity Level	Description	Is Handle
0 - 10	Is only a message or information	No
11 - 16	Violations and programming errors	Yes
17 - 19	Erros about resources like memory or disk space	Yes
20 - 25	Terminate the connection, normaly are critical errors	No

procedure returns the procedure name where the error are origned, an error by ad-hoc or dinamic sql query the procedure name don't returns;
line number means the error line in your code;

Let's see any errors, write and execute this code on SSMS:

RAISERROR('any error', 1, 1);

This returns:

any error
Msg 50000, Level 1, State 1

See the error number 50000, severity level 1, state 1. Note the severity level are 1, therefore it's only a information message, don't abort the query statement, let's make a test:

RAISERROR('Some info', 1, 1);

PRINT('Wow the query was not aborted')

This results:

Some info
Msg 50000, Level 1, State 1
Wow the query was not aborted

The print command are executed after the error, but if we have a severity level up to 10? Let's make use of TRY-CATCH blocks:

BEGIN TRY
  DECLARE @tb TABLE (
    id integer unique
  )

  INSERT INTO @tb
  VALUES(1),(1)

  PRINT('The select was not executed')
  SELECT *
  FROM @tb;
END TRY
BEGIN CATCH
  THROW; --//re raising an error
END CATCH

(0 rows affected)
Msg 2627, Level 14, State 1, Line 7
Violation of UNIQUE KEY constraint 'UQ__#BF78AB8__3213E83ECAD62035'. Cannot insert duplicate key in object 'dbo.@tb'. The duplicate key value is (1).

The statement are termined and the code flow are stopped, going to CATCH block, just for demostration, I "re-raise" the error in the catch block, the THROW command without parameters do this, normaly in the catch block the error are handled. Let's see an error with severity level up to 20:

RAISERROR('Terminate the connection', 20, 1) WITH LOG;

Msg 2745, Level 16, State 2, Line 1
Process ID 52 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Terminate the connection
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Look the severity level 20, and the message "Terminate the connection". For serious errors the connection have to be terminated.

Raising an error using RAISERROR and THROW commands

In SQL Server we can raise an error with two commands RAISERROR or THROW.

RAISERROR

The RAISERROR have this parameters:

Message or message id text or number;
Severity level number;
State number;

Values for parameters markers text or number;

The first parameter is the message error, can be a message id or a text and we can use parameter markers, lets see some code:

RAISERROR('Test', 18, 1);

Msg 50000, Level 18, State 1, Line 1
Test

See the error message are "Test", the severity level 18 and state 1. Let's use parameters markers:

RAISERROR('Error divisor by %s after %d line', 18, 1, 'test', 3);

Msg 50000, Level 18, State 1, Line 1
Error divisor by test after 3 line

With RAISERROR we can use a formated message, and inform the param values after the state param, but we cannot use functions into parameters:

RAISERROR('Error divisor by %s after %d line', 18, 1, CAST(GETDATE() AS DATE), 3);

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CAST'.

Alternatively we can use variables and inform error messages with functions:

DECLARE @errorMessage varchar(100) = ''
       ,@severity     smallint     = 18
       ,@state        tinyint      = 1;

SELECT @errorMessage = 'Error %s (' + CONVERT(VarChar(100), GETDATE(), 121) + ')';

RAISERROR(@errorMessage, @severity, @state, 'division by 0');

Msg 50000, Level 18, State 1, Line 5
Error division by 0 (2018-05-19 16:00:35.147)

Let's add an error message on SQL Server:

PRINT('The message id must be bigger than 50000')

EXEC sys.sp_addmessage @msgnum   = 50000
                      ,@severity = 18 
                      ,@msgtext  = 'Test message'
                      ,@lang     = NULL
                      ,@with_log = NULL
                      ,@replace	 = NULL

The message id must be bigger than 50000, or we get an error:

The message id must be bigger than 50000
Msg 15040, Level 16, State 1, Procedure sys.sp_addmessage, Line 31 [Batch Start Line 0]
User-defined error messages must have a ID greater than 50000.

So using message id greater than 50000:

PRINT('message id 50001 is good')
EXEC sys.sp_addmessage @msgnum   = 50001
                      ,@severity = 18 
                      ,@msgtext  = 'Test message'
                      ,@lang     = NULL
                      ,@with_log = NULL
                      ,@replace	 = NULL

message id 50001 is good

With the message created let's see it on sys.messages:

SELECT *
FROM sys.messages
WHERE message_id = 50001;

The result set:

message_id  language_id severity is_event_logged text
----------- ----------- -------- --------------- -------------
50001       1033        18       0               Test message

Using the message 50001 to raise an error:

RAISERROR(50001, 18, 1);

Msg 50001, Level 18, State 1, Line 28
Test message

If you will not use more the message, drop it with sys.sp_dropmessage:

EXEC sys.sp_dropmessage @msgnum = 50001

RAISERROR options

The RAISERROR have some options that can be informed using WITH command:

NOWAIT
LOG

The NOWAIT is used to issue the error or warning without wait the buffer, it's used to debug a complex codes like procedures or batchs. With the LOG option we can log the error on SQL and Application system log, but the permission to ALTER TRACE is necesary.

THROW

The THROW command are avaliable in SQL Server 2012 version, the command is similar to RAISERROR, this command can be used in two ways:

Raise an error;
Rethrow or reraise an error in CATCH block;

Using THROW to raise a error

The THROW command have this parameters:

error number number
message text

state number

Instead of raiserror we can inform the error number with out then exists in sys.messages, but the error number must be greater or equal than 50000:

THROW 50000, 'test', 1

Msg 50000, Level 16, State 1, Line 1
test

Observe that we cannot specify the severity level, is only 16 when the THROW command is used. We cannot format a message like RAISERROR command, but it's possible use variables, format before pass the parameters to THROW command, try execute this:

DECLARE @errorNumber  integer      = 50001
       ,@errorMessage varchar(100) = 'test'
       ,@state        tinyint      = 1;

SELECT @errorMessage += ' (' + CONVERT(VarChar(50), GETDATE(), 121) + ')'

THROW @errorNumber, @errorMessage, @state;

This returns a compilation error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'THROW'.

The SQL Server considered the THROW an alias to last statement, returning a compilation error, what's gonna happen when we remove the variable and the THROW parameters? Let's see:

DECLARE @errorNumber  integer      = 50001
       ,@errorMessage varchar(100) = 'test'
       ,@state        tinyint      = 1

SELECT ' (' + CONVERT(VarChar(50), GETDATE(), 121) + ')'

THROW

This returns a SELECT statement with a column called "THROW":

THROW
-----------------------------------------------------
 (2018-05-19 18:57:50.283)

When we use the THROW command, it's necessary terminate the last statement using a semicolon ;, separating the commands:

DECLARE @errorNumber  integer      = 50001
       ,@errorMessage varchar(100) = 'test'
       ,@state        tinyint      = 1

SELECT @errorMessage += ' (' + CONVERT(VarChar(50), GETDATE(), 121) + ')';

THROW @errorNumber, @errorMessage, @state;

Msg 50001, Level 16, State 1, Line 7
test (2018-05-19 19:02:44.070)

Many peoples prefer use a semicolon before the command, like ;THROW preventing possible errors. The command don't need parentheses instead the RAISERROR, but don't support the options NOWAIT and LOG.

Using THROW to rethrow an error

To use the THROW to rethrow an error it's necessary inform this on CATCH block, the new raised error is equal to origal error (the number, message, severity, state and line):

BEGIN TRY
  RAISERROR('Test', 18, 1);
END TRY
BEGIN CATCH
  PRINT('In catch block')

  ;THROW
END CATCH

In catch block
Msg 50000, Level 18, State 1, Line 5
Test

Resource	RAISERROR	THROW
Error message by `sys.messages`	Yes	No
Message parameter can contain formated messages	Yes	No
Specify the severity	Yes	No
Rethrow an error	No	Yes

Error functions

Some functions in SQL Server can be used to handle errors, but can be used only in the CATCH BLOCK (if you use at another block these functions returns NULL):

Name	Description	Example
ERROR_MESSAGE	Returns the error message	`SELECT ERROR_MESSAGE()`
ERROR_LINE	Returns the error line in the batch or procedure	`SELECT ERROR_LINE()`
ERROR_NUMBER	Returns the error number	`SELECT ERROR_NUMBER()`
ERROR_PROCEDURE	Returns the proc name, if are a batch statement the function returns `NULL`	`SELECT ERROR_PROCEDURE()`
ERROR_SEVERITY	Returns the error severity	`SELECT ERROR_SEVERITY()`
ERROR_STATE	Returns the error state	`SELECT ERROR_STATE()`

With this functions we can create a robust error handling, combining the functions and return a message or a error more friendly. As mentioned this functions only return any result if they are called from a CATCH BLOCK, but we can call functions and procedures that handle the errors internally and returns a message, being possible reuse the error handling in another batchs or procedures. Let's create a function to handle errors:

CREATE FUNCTION dbo.fn_handleError()
RETURNS VarChar(300)
AS
BEGIN
  DECLARE @ErrorMessage VarChar(300) = '';

  --//Char(13) is a enter key or break line
  SELECT @ErrorMessage = CASE WHEN ERROR_PROCEDURE() IS NULL --//a null handle if we concat NULL + any value returns null
                           THEN ''
                           ELSE 'Procedure Name: ' + ERROR_PROCEDURE() + ' ' + CHAR(13)
                         END +
                         'Error Line: ' + CAST(ERROR_LINE() AS VarChar(10)) + ' ' + CHAR(13) +
                         'Error Message: ' + ERROR_MESSAGE() + ' ' + CHAR(13) + 
                         'Error Number: ' + CAST(ERROR_NUMBER() AS VarChar(10)) + ' ' + CHAR(13) +
                         'Error State: ' + CAST(ERROR_STATE() AS VarChar(10)) + ' ' + CHAR(13) +
                         'Error Severity: ' + CAST(ERROR_SEVERITY() AS VarChar(10));
  RETURN @ErrorMessage;
END

Using the function in a batch statement:

BEGIN TRY
  SELECT 1/0;
  SELECT 'This SELECT does not execute';
END TRY
BEGIN CATCH
  SELECT dbo.fn_handleError();
END CATCH

Returns:

Error Line: 2
Error Message: Divide by zero error encountered.
Error Number: 8134
Error State: 1
Error Severity: 16

Now let's create a procedure to raise a error:

CREATE PROCEDURE dbo.sp_raiseError 
AS 
BEGIN
  BEGIN TRY
    SELECT 1/0 
    SELECT 'This select does not execute'
  END TRY
  BEGIN CATCH
    SELECT dbo.fn_handleError();
  END CATCH
END

Execute the procedure:

EXECUTE dbo.sp_raiseError;

Result:

Procedure Name: sp_raiseError
Error Line: 5
Error Message: Divide by zero error encountered.
Error Number: 8134
Error State: 1
Error Severity: 16

Observe the first line with the procedure name.

Dropping the objects from database:

DROP PROCEDURE dbo.sp_raiseError;
DROP FUNCTION dbo.fn_handleError;

@@Error

In SQL Server the @@ERROR system function can be used to handle a error or identify a error, the functions returns 0 if any error occurs and the last command is successfully executed else some error occurs:

DECLARE @tb_Error TABLE (
  ID   Integer
 ,Name VarChar(300) UNIQUE
);

INSERT INTO @tb_Error
VALUES(1, 'Hidenki');

IF @@ERROR <> 0
  PRINT('Some error occur!');

(1 row affected)

Let's force a error:

DECLARE @tb_Error TABLE (
  ID   Integer
 ,Name VarChar(300) UNIQUE
);

INSERT INTO @tb_Error
VALUES(1, 'Hidenki'), (2, 'Hidenki');

IF @@ERROR <> 0
  PRINT('Some error occur!');

The OUTPUT:

Msg 2627, Level 14, State 1, Line 6
Violation of UNIQUE KEY constraint 'UQ__#AA7DC77__737584F615E6E5DE'. Cannot insert duplicate key in object 'dbo.@tb_Error'. The duplicate key value is (Hidenki).
The statement has been terminated.
Some error occur!

With this function it's possible terminate the batch or apply a handle.

Transacions

Concepts

When we talk about transactions we should consider the concepts of ACID:

Atomicity - a transaction should be a whole or nothing, cannot be separated, if a error occurs the transaction should be reverted else the transacion is commited.
Consistency - a transaction should respect the data integrity, ensuring the consistent state of a database.
Isolation - no one transaction should access the same data that are using in another transaction, a transaction should be isolated and cannot see the alterations of others transactions.
Durability - if the transaction are successfully executed, the commit ensure the data modified in the transaction, consisting the data on the database.

Handle transactions

To start a transction we can use the commands:

BEGIN TRAN; --//abbreviated form to write TRANSACTION
BEGIN TRANSACTION;

To commit a opened transaction:

COMMIT TRAN; --//abbreviated form
COMMIT TRANSACTION;
COMMIT WORK;
COMMIT; -- or just commit

If a error occurs we can revert a opened transacion:

ROLLBACK;
ROLLBACK TRAN;
ROLLBACK WORK;
ROLLBACK TRANSACTION;

Now we know the basic commands to work with transactions, let's see a example to use them:

IF OBJECT_ID('dbo.Test')  IS NOT NULL
  DROP TABLE dbo.Test;

CREATE TABLE dbo.Test(
  ID     Integer Identity(1,1)
 ,Name   VarChar(150)
 ,Birth  Date
 ,Height Real
);

BEGIN TRAN; --//Starting a transaction

INSERT INTO dbo.Test(
  Name
 ,Birth
 ,Height
)
Values('Hidekin'
      ,'19990101'
      ,1.79);

COMMIT TRAN; --//commit

BEGIN TRAN;

INSERT INTO dbo.Test(
  Name
 ,Birth
 ,Height
)
Values('Hidekis'
      ,'19990101'
      ,1.88);

ROLLBACK;

SELECT * FROM dbo.Test;

The resultset:

ID          Name                            Birth      Height
----------- ------------------------------- ---------- -------------
1           Hidekin                         1999-01-01 1,79

Observe the unique record is the "Hidekin", the "Hidekis" aren't inserted, now look the commands used in each INSERT, the first use BEGIN TRAN and COMMIT, this start a transaction and save the alterations on database using the COMMIT, finishing the transacion. The second insert use the BEGIN TRAN and ROLLBACK, that is, I insert a record but a ROLLBACK are called to revert the alterations before the transaction starts, removing the record inserted.

Use this command to drop the table:

DROP TABLE dbo.Test;

Implicit Transactions

In SQL Server each command that modify or insert a data have a implicit transaction, if some error (check constraints, foreign keys or validations) occurs during the execution the SQL Server revert the alterations, but if a explicit transaction are issued the SQL Server don't open a implicit transaction for each modification, the programmer choice when COMMIT (to save the alterations) or ROLLBACK (to revert the alterations) the transaction.

Nested Transactions and @@TRANCOUNT

In SQL Server it's possible open nested transactions, that is, we can open more than one transaction per session, but we need one commit for each opened transaction. The SQL Server provides a function called @@TRANCOUNT, this function returns the number of transactions opened, let's see:

SET NOCOUNT ON;

BEGIN TRAN;

BEGIN TRAN;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

COMMIT;


PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));
COMMIT;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

We can open multiples transactions, but in SQL Server, nested transactions are only illustrative because if a COMMIT are issue and @@TRANCOUNT is 2 after the commit the trancount gonna be 1, but the data aren't commited if a rollback are issued all the alterations gonna be reverted, let's see:

IF OBJECT_ID('dbo.Test') IS NOT NULL
  DROP TABLE dbo.Test;

CREATE TABLE dbo.Test(Id Integer Identity(1,1)
                     ,Name VarChar(80));

BEGIN TRAN;

PRINT('Inserting RONALD');

INSERT INTO dbo.Test(Name)
VALUES('RONALD');

BEGIN TRAN;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

PRINT('Inserting PELÉ');
INSERT INTO dbo.Test(Name)
VALUES('PELÉ');

COMMIT;
PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

SELECT * FROM dbo.Test;

ROLLBACK;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));
SELECT * FROM dbo.Test;

IF OBJECT_ID('dbo.Test') IS NOT NULL
  DROP TABLE dbo.Test;

The result:

Inserting RONALD
Total transactions: 2
Inserting PELÉ
Total transactions: 1

Id          Name
----------- ---------------------
1           RONALD
2           PELÉ

Total transactions: 0

Id          Name
----------- ---------------------

Note the second insert, "PELÉ" are inserted and commited but the first transaction (that insert "RONALD") should be the unique transaction reverted but all the transactions are reverted, the SQL Server consider only the first transaction to save the alterations to database, in other words only when the @TRANCOUN is 1 the commit really gonna save the alterations on database. Other interesting thing in SQL Server about the transactions is the ROLLBACK, if more than 1 transaction is open the ROLLBACK revert all transactions and the @@TRANCOUNT is 0:

BEGIN TRAN;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));
BEGIN TRAN;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

ROLLBACK;

PRINT('Total transactions: ' + CAST(@@TRANCOUNT AS VarChar(10)));

This returns:

Total transactions: 1
Total transactions: 2
Total transactions: 0

Transactions with variables

The transactions don't affect the variables and table variables, so if we need to save any data and can occur a rollback it's possible save the data in a variable or table variable, let's see:

DECLARE @tb_Test TABLE(FirstName VarChar(100)
                      ,BirthDate Date);

IF OBJECT_ID('dbo.test') IS NOT NULL
  DROP TABLE dbo.test;
 
CREATE TABLE dbo.test (FirstName VarChar(100)
                      ,BirthDate Date);

BEGIN TRAN;

INSERT INTO dbo.test 
VALUES('ADRIANO', '19901021');

INSERT INTO @tb_Test(FirstName
                    ,BirthDate)
SELECT FirstName
      ,BirthDate
FROM dbo.test;


ROLLBACK;


PRINT('SELECT FROM a fisic table');
SELECT * FROM dbo.test;

PRINT('SELECT FROM a variable table');
SELECT * FROM @tb_Test;

IF OBJECT_ID('dbo.test') IS NOT NULL
  DROP TABLE dbo.test;

The result:

SELECT FROM a fisic table
FirstName                                 BirthDate
----------------------------------------- ----------

SELECT FROM a variable table
FirstName                                 BirthDate
----------------------------------------- ----------
ADRIANO                                   1990-10-21

Transactions and DDL commands

In SQL Server the DDL (data definition language) commands are transactional, that is we can create a table in a transaction and revert this, the table don't will more exists, this apply to views, procedures, functions and other DDL commands:

BEGIN TRAN;
CREATE TABLE dbo.test (ID Integer);

SELECT * FROM dbo.test;

ROLLBACK;

SELECT * FROM dbo.test;

This returns:

ID
-----------

Msg 208, Level 16, State 1, Line 8
Nome de objeto 'dbo.test' inválido.

The last SELECT returns a error because the table don't exists after the transaction.

Named tranasctions

Alternatively we can give a name to transactions using variables or a direct name:

DECLARE @tran1 VarChar(30) = 'tran1';

BEGIN TRAN @tran1;

COMMIT TRAN @tran1;

BEGIN TRAN tran2;

ROLLBACK TRAN tran2;

Savepoints

Savepoints are like the name suggests, only in a opened transaction we can start a savepoint, this is used like a sublevel to a transaction if any thing is wrong after start the savepoint we can revert to the state before the savepoint is started, a savepoint can be reverted but cannot be commited, you need to commit the transaction and not the savepoint, the command to start a savepoint is SAVE TRAN [savepoint name] and to ROLLBACK a tran ROLLBACK TRAN [savepoint name]:

IF OBJECT_ID('dbo.test') IS NOT NULL
  DROP TABLE dbo.test;

CREATE TABLE dbo.test(Name VarChar(100));

DECLARE @save1 VarChar(30) = 'save1';

BEGIN TRAN;

INSERT INTO dbo.test VALUES('name1');

SAVE TRAN @save1;
PRINT('TRANCOUNT after start a savepoint: ' + CAST(@@TRANCOUNT AS VarChar(10)));

INSERT INTO dbo.test VALUES('name2');

SAVE TRAN savepoint2;

INSERT INTO dbo.test VALUES('name3');

ROLLBACK TRAN savepoint2;

PRINT('After rollback savepoint2:');
SELECT * FROM dbo.test;

ROLLBACK TRAN @save1;

PRINT('After rollback save1:');
SELECT * FROM dbo.test;

ROLLBACK TRAN;

SELECT @@TRANCOUNT;
IF OBJECT_ID('dbo.test') IS NOT NULL
  DROP TABLE dbo.test;

This returns:

TRANCOUNT after start a savepoint: 1

After rollback savepoint2:
Name
----------------------------------------
name1
name2

After rollback save1:
Name
----------------------------------------
name1


-----------
0

The name1 are inserted before any savepoint, name2 are inserted after the savepoint "save1", and name3 after "savepoint2", if we rollback the savepoint "savepoint2" the name3 are reverted and only name2 and name1 exists in the table, if we rollback the savepoint "save1" the name2 are removed and remains name1. Observe that we can have a savepoint after a started savepoint but if we rollback the first savepoint the second doesn't exists:

BEGIN TRAN

SAVE TRAN s1;
PRINT('Start the savepoint "s1"');

SAVE TRAN s2;
PRINT('Start the savepoint "s2"');

ROLLBACK TRANSACTION s1;
PRINT('Rollback the savepoint "s1"');

ROLLBACK TRANSACTION s2;
PRINT('Rollback the savepoint "s2"');

ROLLBACK;

This returns a error:

Start the savepoint "s1"
Start the savepoint "s2"
Rollback the savepoint "s1"
Msg 6401, Level 16, State 1, Line 12
Cannot roll back s2. No transaction or savepoint of that name was found.
Rollback the savepoint "s2"

The correct order is:

BEGIN TRAN

SAVE TRAN s1;
PRINT('Start the savepoint "s1"');

SAVE TRAN s2;
PRINT('Start the savepoint "s2"');

ROLLBACK TRANSACTION s2;
PRINT('Rollback the savepoint "s2"');

ROLLBACK TRANSACTION s1;
PRINT('Rollback the savepoint "s1"');

ROLLBACK;

Start the savepoint "s1"
Start the savepoint "s2"
Rollback the savepoint "s2"
Rollback the savepoint "s1"

Transactions WITH MARK

The SQL Server provides the option to mark a transaction, it's can be used to mark the transaction name in the LOG of SQL Server, you can use this to rocover or restore a consistently of a database, for more informations read the oficial documentation.

Handle error and transactions

Think in this case, if a transaction is open and a error occurs what's happen? The transaction stay open or gonna be closed? The alterations are saved or reverted? Now we know about error handling and transactions, if a error occurs by default the SQL Server don't rollback the transaction or revert the alterations, but this can be handled using the TRY and CATCH blocks to catch a error and the @@TRANCOUNT to handle transacions, let's see a example:

SET NOCOUNT ON; --//this command doesn't returns how many rows are affected in a insert / update / delete
                --//it's a good pratice in performance terms
DECLARE @trancount       smallint     = 0
       ,@ErrorMessage    varchar(200) = ''
       ,@ErrorSeverity   smallint     = 0
       ,@ErrorLine       int          = 0
       ,@ErrorState      smallint     = 0
       ,@ErrorProcedure  varchar(200) = ''
       ,@ErrorNumber     int          = 0;

BEGIN TRY

  SELECT @trancount = @@TRANCOUNT;

  IF (@trancount = 0) --//if a transaction already open no one transaction going to be started
    BEGIN TRANSACTION; --//if neither transaction are opened we start a transaction

  /*
  Your code here!
  */


  IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we commit this
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  --//remember - you can use a procedure or function to get this informations and format then
  --//           but the function/procedure need to be called in the catch block (this block)
  SELECT @ErrorMessage   = ERROR_MESSAGE()
        ,@ErrorSeverity  = ERROR_SEVERITY()
        ,@ErrorLine      = ERROR_LINE()
        ,@ErrorNumber    = ERROR_NUMBER()
        ,@ErrorState     = ERROR_STATE()
        ,@ErrorProcedure = ERROR_PROCEDURE();

  IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we rollback this
    ROLLBACK;

  SELECT CASE WHEN @ErrorProcedure IS NULL --//a null handle if we concat NULL + any value returns null
           THEN ''
           ELSE 'Procedure Name: ' + @ErrorProcedure + ' ' + CHAR(13)
         END +
         'Error Line: '     + CAST(@ErrorLine AS VarChar(10)) + ' ' + CHAR(13) +
         'Error Message: '  + @ErrorMessage + ' ' + CHAR(13) +
         'Error Number: '   + CAST(@ErrorNumber AS VarChar(10)) + ' ' + CHAR(13) +
         'Error State: '    + CAST(@ErrorState  AS VarChar(10)) + ' ' + CHAR(13) +
         'Error Severity: ' + CAST(@ErrorSeverity AS VarChar(10));
END CATCH

Look the batch model, first before the TRY block we declare the variables, in the TRY block i see if any transaction is open, if this situiation is true we don't need to open a transaction because this already at a opened transaction (if we open more than one transaction, it's gonna be a nested transaction and will need a correspondent numbers of commits, to evite any error it's better don't open chained transactions), else we open a transaction, after we open a transaction you put your code. After you do waht you need to do in the transaction we verify, if the trancount is 0 and the @@TRANCOUNT is greater than 0, in this batch we start a transaction and we need to commit this. Otherwise if we get a error in the batch the CATCH block are fired and we verify, if a transaction is opened in this batch we revert the alterations issuing a ROLLBACK and after return the error details. Let's see a example:

IF OBJECT_ID('dbo.test') IS NOT NULL
  DROP TABLE dbo.test;

CREATE TABLE dbo.test (
  ID        Integer NOT NULL
 ,Name      VarChar(100) CHECK (Name LIKE '%[A]%')
 ,BirthDate DateTime CHECK (BirthDate > '19000101')
)

SET NOCOUNT ON;
DECLARE @trancount       smallint     = 0
       ,@ErrorMessage    varchar(200) = ''
       ,@ErrorSeverity   smallint     = 0
       ,@ErrorLine       int          = 0
       ,@ErrorState      smallint     = 0
       ,@ErrorProcedure  varchar(200) = ''
       ,@ErrorNumber     int          = 0;

BEGIN TRY

  SELECT @trancount = @@TRANCOUNT;

  IF (@trancount = 0) --//if a transaction already open no one transaction going to be started
    BEGIN TRANSACTION; --//if neither transaction are opened we start a transaction

  PRINT('Numbers of tran: ' + CAST(@@TRANCOUNT AS VarChar(10)));

  INSERT INTO dbo.test(ID 
                      ,Name
                      ,BirthDate)
  VALUES(1
        ,'Liza'
        , GETDATE());

  IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we commit this
    COMMIT TRANSACTION;

  PRINT('Numbers of tran: ' + CAST(@@TRANCOUNT AS VarChar(10)));
END TRY
BEGIN CATCH
  --//remember - you can use a procedure or function to get this informations and format then
  --//           but the function/procedure need to be called in the catch block (this block)
  SELECT @ErrorMessage   = ERROR_MESSAGE()
        ,@ErrorSeverity  = ERROR_SEVERITY()
        ,@ErrorLine      = ERROR_LINE()
        ,@ErrorNumber    = ERROR_NUMBER()
        ,@ErrorState     = ERROR_STATE()
        ,@ErrorProcedure = ERROR_PROCEDURE();

  IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we rollback this
    ROLLBACK;

  SELECT CASE WHEN @ErrorProcedure IS NULL --//a null handle if we concat NULL + any value returns null
           THEN ''
           ELSE 'Procedure Name: ' + @ErrorProcedure + ' ' + CHAR(13)
         END +
         'Error Line: '     + CAST(@ErrorLine AS VarChar(10)) + ' ' + CHAR(13) +
         'Error Message: '  + @ErrorMessage + ' ' + CHAR(13) +
         'Error Number: '   + CAST(@ErrorNumber AS VarChar(10)) + ' ' + CHAR(13) +
         'Error State: '    + CAST(@ErrorState  AS VarChar(10)) + ' ' + CHAR(13) +
         'Error Severity: ' + CAST(@ErrorSeverity AS VarChar(10));
END CATCH

SELECT * FROM dbo.test

This returns:

Numbers of tran: 1
Numbers of tran: 0
ID          Name           BirthDate
----------- -------------- -----------------------
1           Liza           2018-05-28 20:20:46.010

This command don't get any error, open a transaction, insert a record and commit the transaciont, saving the alterations on database. Let's create a test procedure:

IF OBJECT_ID('dbo.sp_InsertTest') IS NOT NULL
  DROP PROCEDURE dbo.sp_InsertTest;

GO

CREATE PROCEDURE dbo.sp_InsertTest @ID        int
                                  ,@Name      varchar(100)
                                  ,@BirthDate datetime
AS 
BEGIN
  SET NOCOUNT ON;
  DECLARE @trancount       smallint     = 0
         ,@ErrorMessage    varchar(200) = ''
         ,@ErrorSeverity   smallint     = 0
         ,@ErrorLine       int          = 0
         ,@ErrorState      smallint     = 0
         ,@ErrorProcedure  varchar(200) = ''
         ,@ErrorNumber     int          = 0;

  BEGIN TRY

    SELECT @trancount = @@TRANCOUNT;

    IF (@trancount = 0) --//if a transaction already open no one transaction going to be started
      BEGIN TRANSACTION; --//if neither transaction are opened we start a transaction

    PRINT('sp_InsertTest - open a transaction - numbers of tran: ' + CAST(@@TRANCOUNT AS VarChar(10)));

    INSERT INTO dbo.test(ID 
                        ,Name
                        ,BirthDate)
    VALUES(@ID
          ,@Name
          ,@BirthDate);

    IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we commit this
      COMMIT TRANSACTION;

    PRINT('sp_InsertTest - COMMMIT the transaction - numbers of tran: ' + CAST(@@TRANCOUNT AS VarChar(10)));
  END TRY
  BEGIN CATCH
    --//remember - you can use a procedure or function to get this informations and format then
    --//           but the function/procedure need to be called in the catch block (this block)
    SELECT @ErrorMessage   = ERROR_MESSAGE()
          ,@ErrorSeverity  = ERROR_SEVERITY()
          ,@ErrorLine      = ERROR_LINE()
          ,@ErrorNumber    = ERROR_NUMBER()
          ,@ErrorState     = ERROR_STATE()
          ,@ErrorProcedure = ERROR_PROCEDURE();

    IF (@trancount = 0 AND @@TRANCOUNT > 0) --//if in this batch a transaction are started we rollback this
      ROLLBACK;

    PRINT('sp_InsertTest - rollback a transaction - numbers of tran: ' + CAST(@@TRANCOUNT AS VarChar(10)));

    SELECT CASE WHEN @ErrorProcedure IS NULL --//a null handle if we concat NULL + any value returns null
             THEN ''
             ELSE 'Procedure Name: ' + @ErrorProcedure + ' ' + CHAR(13)
           END +
           'Error Line: '     + CAST(@ErrorLine AS VarChar(10)) + ' ' + CHAR(13) +
           'Error Message: '  + @ErrorMessage + ' ' + CHAR(13) +
           'Error Number: '   + CAST(@ErrorNumber AS VarChar(10)) + ' ' + CHAR(13) +
           'Error State: '    + CAST(@ErrorState  AS VarChar(10)) + ' ' + CHAR(13) +
           'Error Severity: ' + CAST(@ErrorSeverity AS VarChar(10)) AS ErrorDesc; 
  END CATCH
END

Now execute the procedure:

EXECUTE dbo.sp_InsertTest @ID        = 1
                         ,@Name      = 'Adrians'
                         ,@BirthDate = '19971212'

SELECT * FROM dbo.test;

This returns:

sp_InsertTest - open a transaction - numbers of tran: 1
sp_InsertTest - COMMMIT the transaction - numbers of tran: 0
ID          Name                                                                                                 BirthDate
----------- ---------------------------------------------------------------------------------------------------- -----------------------
1           Liza                                                                                                 2018-05-28 20:20:46.010
1           Adrians                                                                                              1997-12-12 00:00:00.000

If we force a error? What's happen?

EXECUTE dbo.sp_InsertTest @ID        = 1
                         ,@Name      = 'Pele'
                         ,@BirthDate = '19551212'


SELECT * FROM dbo.test;

sp_InsertTest - open a transaction - numbers of tran: 1
sp_InsertTest - rollback a transaction - numbers of tran: 0

ErrorDesc
--------------------------------------------------------------------------------------
Procedure Name: dbo.sp_InsertTest
Error Line: 25
Error Message: The INSERT statement conflicted with the CHECK constraint "CK__test__Name__6A30C649". The conflict occurred in database "teste", table "dbo.test", column 'Name'.
Error Number: 547
Error State: 0
Error Severity: 16

ID          Name                                                                                                 BirthDate
----------- ---------------------------------------------------------------------------------------------------- -----------------------
1           Liza                                                                                                 2018-05-28 20:20:46.010
1           Adrians                                                                                              1997-12-12 00:00:00.000

Now we catch an error, look at the error message, the constraint that valid if the field Name have the leter 'a' in any position, the name "Pele" don't have the leter 'a', rainsing a error and rollback the transaction setting the numbers of transactions to 0.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

3.2.Handling_errors_and_transactions.md

3.2.Handling_errors_and_transactions.md

3.2. Handling errors and transactions

Errors in the SQL Server

Raising an error using RAISERROR and THROW commands

RAISERROR

RAISERROR options

THROW

Using THROW to raise a error

Using THROW to rethrow an error

Error functions

@@Error

Transacions

Concepts

Handle transactions

Implicit Transactions

Nested Transactions and @@TRANCOUNT

Transactions with variables

Transactions and DDL commands

Named tranasctions

Savepoints

Transactions WITH MARK

Handle error and transactions

Files

3.2.Handling_errors_and_transactions.md

Latest commit

History

3.2.Handling_errors_and_transactions.md

File metadata and controls

3.2. Handling errors and transactions

Errors in the SQL Server

Raising an error using RAISERROR and THROW commands

RAISERROR

RAISERROR options

THROW

Using THROW to raise a error

Using THROW to rethrow an error

Error functions

@@Error

Transacions

Concepts

Handle transactions

Implicit Transactions

Nested Transactions and @@TRANCOUNT

Transactions with variables

Transactions and DDL commands

Named tranasctions

Savepoints

Transactions WITH MARK

Handle error and transactions