Errors Handling in SQL Server

List of System-Defined Error Messages

We can use sys. messages Table to look for the specific errors with their respective error numbers.

select top 10 * from sys.messages (NOLOCK)

USING “@@ERROR”

@@ERROR Will return a value of Zero if there is no error in the previous statements. In the below example there is no error encountered, So running the @ERROR will give us a value of zero.

--THE BELOW STATEMENT DOESNT HAVE ANY ERROR SO "@@ERROR" WILL RETURN A VALE OF ZERO.
select 1/1 'Row Without Error';
--BELOW STATEMENT WILL RETURN A VALUE OF ZERO.
select @@ERROR '@@ERROR Output';

If we run the below statement we will obviously get a divide by zero error and if we run “@@ERROR” immediately after the below statement we will not get a value of zeros Hence an error has been encountered and that information Will Be Logged into our sys. messages

--THE BELOW STATEMENT WILL GIVE THE FOLLOWING ERROR MESSAGE 'Divide by zero error encountered.'
select 1/0;
--BELOW STATEMENT WILL GIVE THE ERROR CODE BUT THIS SHOULD RUN IMMEDIATLE AFTER THE ABOVE STATEMENT
select @@ERROR;

SELECT * FROM SYS.MESSAGES WHERE message_id=8134 AND language_id=1033

CHECKING ERROR LOGS

--RUN THIS COMMAND TO START THE NEW ERROR LOG
--PLEASE DO NOT RUN IN PRODUCTION ENVIRONMENT
exec sp_cycle_errorlog

To execute sp_cycle_errorlog you need to be a member of the sysadmin role.

RAISERROR

Raise error is used to Raise Error Manually. Sometimes there are errors while running SQL Statements and you really don’t have much information about the actual error, But you wish you could.

with RAISE ERROR you can generate your own Custom Error Messages.

RAISERROR('This is not a Drill',16,1);GO

“WITH LOG” Option

You can also use the “WITH LOG” option to log the error message in the log file.

RAISERROR('This is not a Drill',16,1) WITH LOG;

GO

You can also use a variable to store the Error Text Message and then use that variable in your RAISERROR to show that error text.

DECLARE @ErrorText nvarchar(100);
SET @ErrorText='This is a Variable Generated Error Message';

RAISERROR(@ErrorText,16,1);
GO

“WITH NOWAIT” Option

“WITH NOWAIT” Option the user will be notified immediately about the error message, with this option SQL server will not wait for the entire SQL statement to complete.

RAISERROR('I do not want to wait for 5 Minutes to send mail to client',16,1) WITH NOWAIT;WAITFOR DELAY '00:05:00'GO

If the “NO WAIT” Option is not there the SQL Server will wait for 5 minutes Before raising the error to the user

TRY..CATCH Blocks

TRY/CATCH blocks for handling errors in SQL were first introduced in 2005, Before TRY/CATCH were introduced, @ERROR was the popular method used to handle errors in SQL servers, @ERROR in conjunction with GOTO Command was used to skip the code and rollback the transaction.

A TRY/CATCH Block can simplify the process of catching an error and taking some action on it. the TRY Portion of the TRY/CATCH Block can be used to perform an action like INSERTING a row in a Table or UPDATING or DELEING a row from a table if any error is encountered in the TRY portion, The control is then passed to the CATCH portion if the TRY/CATCH Block where you can write a code to ROLLBACK the transaction or Return an Error Message.

• For every BEGIN TRY and END TRY there Should be corresponding BEGIN CATCH and END CATCH Block

• The TRY/CATCH Block can handle Error Messages of certain severity levels Only

• The TRY/CATCH Block can handle severity levels Greater than 10 and less than 20 

• We cannot use a TRY/CATCH block inside a User Defined Function 

• TRY/CATCH Block can be nested, that is inside of the CATCH you perform another TRY. 

TRY/CATCH General Syntax

BEGIN TRY 
--PERFORM AN ACTION
END TRY

BEGIN CATCH
--PERFOM AN ACTION
END CATCH

Below is an example of a TRY Catch Block, We will create a Table EMPLOYEES, and we will try to insert a duplicate record which is a violation of the Primary key Constraint as the “ID” Column is a Primary key and will not allow duplicates

IF OBJECT_ID(N'tempdb.dbo.##EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE tempdb.dbo.##EMPLOYEES;

CREATE TABLE tempdb.dbo.##EMPLOYEES
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO tempdb.dbo.##EMPLOYEES VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from tempdb.dbo.##EMPLOYEES;


BEGIN TRY 
INSERT INTO tempdb.dbo.##EMPLOYEES VALUES (1,'SHANE','RAY',5000,'IT');
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() ERROR_NUMBER, ERROR_SEVERITY() ERROR_SEVERITY, 
ERROR_STATE() ERROR_STATE, ERROR_PROCEDURE() ERROR_PROCEDURE, 
ERROR_LINE() ERROR_LINE, ERROR_MESSAGE() ERROR_MESSAGE;
END CATCH
--select * from tempdb.dbo.##EMPLOYEES;

XACT_ABORT and XACT_STATE

When you are not planning to use the TRY/CATCH Block, Another statement that can come in handy is SET XACT-ABORT ON. By default, XACT-ABORT is in OFF State when you open a connection. When XACT-ABORT is turned on and a runtime error is encountered, the entire transaction is terminated and rolled back.

XACT_STATE Can be used to determine the state of the current transaction.

IF (XACT_STATE() ) =  0 -- NO CONNECTION 

IF (XACT_STATE() ) =  1 -- COMMIABLE TRANSACTION

IF (XACT_STATE() ) = -1 -- UNCOMMITABLE TRANSACTION

In the Below example, while XACT_ABORT is turned ON, AND we are using an explicit transaction, the Below Temp Table “#TESTTable” will not be created as the whole transaction is rolled back as the error was encountered during the transaction.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
CREATE TABLE #TESTTab1e (ID INT);
SELECT 1/0;
COMMIT TRANSACTION;
GO

In the Below example, while XACT_ABORT is turned OFF, the Below Temp Table “#TESTTable” will be created as the whole transaction is not rolled back even if an error was encountered during the transaction.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
CREATE TABLE #TstTab1e (ID INT);
SELECT 1/0;
COMMIT TRANSACTION;
GO
--THIS TABLE WILL BE CREATED
select id from #TstTab1e;

IF OBJECT_ID(N'tempdb.dbo.##EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE tempdb.dbo.##EMPLOYEES;

CREATE TABLE tempdb.dbo.##EMPLOYEES
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO tempdb.dbo.##EMPLOYEES VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from tempdb.dbo.##EMPLOYEES;

SET XACT_ABORT ON;
BEGIN TRY 
BEGIN TRAN
INSERT INTO tempdb.dbo.##EMPLOYEES VALUES (4,'RAJA','RAO',8000,'HR');
INSERT INTO tempdb.dbo.##EMPLOYEES VALUES (1,'SHANE','RAY',5000,'IT');
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() ERROR_NUMBER, ERROR_SEVERITY() ERROR_SEVERITY, 
ERROR_STATE() ERROR_STATE, ERROR_PROCEDURE() ERROR_PROCEDURE, 
ERROR_LINE() ERROR_LINE, ERROR_MESSAGE() ERROR_MESSAGE;
END CATCH
select * from tempdb.dbo.##EMPLOYEES;

THROW

THROW was first introduced in SQL 2012 and it was introduced as an Improvement to RAISE ERROR. 

• THROW can re-raise the system error message which is generated by SQL.