Raise errors

It is always a good idea to include an error handling in your SQL scripts. Error might generate due to improper during execution time, when we lose resources or connection or even we might generate errors to redirect the execution in stored procedures when certain criteria are met.

As I discussed earlier we could raise error for lot of purpose if we want to check not/availability if a certain value and in a table or check if a table is empty or not then raise error of our own to change the execution direction.

For SQL Server 2000 and earlier version we could use @@ERROR handle error.

As you can see in the error message the error number is returned as 0(zero) because @@ERROR is cleared and reset after each statement executed. In order to avoid this we can assign it to a variable and use is multiple times.

ERROR Handling In SQL Server -Step2

For SQL Server 2005 and later version we could use TRY CATCH instead of @@ERROR. This works by wrapping the code that you want to test for errors in a TRY block and handling the error in the CATCH block. If an error is detected in the TRY statement the control will be transferred to CATCH block.

Severity between 11 and 19 will cause the control to transfer to CATCH statement. Severity 20 or above, compilation error and some runtime error such as calling to non-existing objects will not pass to catch block but it will simply fails.

It is possible to use functions ERROR_NUMBER and ERROR_MESSAGE in CATCH statement.

ERROR Handling In SQL Server -Step3

We may use THROW command if we are on SQL Server 2012 or RAISERROR if you are using previous versions to raise errors of our own.

ERROR Number ranges (1-49999) for SQL Server error and >=50000 for customer errors

SEVERITY Level ranges (0-25). Severity >=16 will be logged to SQL Server and Windows application log. Severity [19-25] can only be specified by members of sysAdmin and Severity levels 20-25 are considered and it will cause for connection termination and transaction rollback. Severity 0-10 has only informational purpose and severity 11-19 will cause execution to jump to the CATCH block.

STATE has <=127 and it is for Microsoft internal purpose

ERROR message should be <=255 Unicode characters.
RAISERROR can have the following format.

BEGIN TRY
   RAISERROR ('RAISERROR Message',
   16,                      -- Severity
   1                        -- State
);
END TRY
BEGIN CATCH
END CATCH;

Or it can have PRINTF format

BEGIN TRY
   RAISERROR (N'Error occurred on %s on severity %d.', -- Message text.
   5,                           -- Severity,
   1,                           -- State,
   N'sp_test',                  -- First argument,
   5);                          -- Second argument.
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER();
END CATCH;

THROW can have the following format

THROW ErrorNumber, N’ErrorMessage’, State;

Unlike RAISERROR, THROW with parameters will always terminate the batch and anything below throw will not execute.