Sql server error handling best practices

As a sql developer, we must learn how to handle error in sql code and log them properly so other developer can fix them quickly.

In real-time SQL programming often we need to catch application error to find the root cause, like any other programming language, here we learn the best way to deal with error and catch exception in sql server database.

Begin Try 

End Try
Begin Catch

End Catch

SQL Error handling helps us getting control over Transact-SQL code, as a developer whenever we write any SQL code that runs based on input, if input data is not as expected that may cause error in code, so we should implement error handing whenever we write any sql server stored procedure or triggers, cursor etc, error can happen while inserting, or updating data, also sometimes at the time of deleting data if there is foreign key reference

Error Handling in SQL Server

So, let's look at how to implement sql server error handling with try, catch , throw, raiserror

Like any other programming language there is a technique of error handling in SQL Server query during database development.

SQL Server has two types of exceptions:

• System Defined
• User Defined

Here are few built-in functions that can help us getting error details, We can use all those function together and capture error of any DML statement

• ERROR_NUMBER

  This will tell us about internal number of the error

• ERROR_STATE

  Returns the information about the source

• ERROR_SEVERITY
• ERROR_LINE

  tell you where the error is, very helpful

• ERROR_PROCEDURE

  Returns the name of the stored procedure or function

• ERROR_MESSAGE

  this will get the error details

Here is how Sql insert error handling can be done, also see how Sql error message displayed using error_message()

Note: In SQL Server RAISERROR and THROW both statements are used to raise an error. RAISERROR was introduced from SQL Server 7.0, where as the journey of THROW statement has just began with SQL Server 2012

RAISERROR eventually will be replaced by THROW

Now we can use RAISERROR inside catch block like

RAISERROR(ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE())

Here is a simple example how we can get all error details as a row with the help of try catch block, this way we can implement error handling in any stored procedure, trigger, cursor etc.

BEGIN TRY
    /* Generate a divide-by-zero error  , you can write your insert or update statment */ 
	SELECT	(0/0) AS Error;
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

The best way to improve your programme would be capturing all sql errors and store them in a SQL table, that will help your developers to understand the error message and the source of error, so they can analyse and resolve the error.

Create a simple table in your database with following SQL code

CREATE TABLE tbErrors
		(ErrorID        INT IDENTITY(1, 1),
		UserName       VARCHAR(100),
		ErrorNumber    INT,
		ErrorState     INT,
		ErrorSeverity  INT,
		ErrorLine      INT,
		ErrorProcedure VARCHAR(MAX),
		ErrorMessage   VARCHAR(MAX),
		ErrorDateTime  DATETIME)
GO

Now everywhere inside catch statement write an insert statement that will insert all error details in the above table, so it will be easy to diagnosis and understand the root cause of the error

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location'

We also can get error log information by executing SP_READERRORLOG, but to execute this sp user need to have securityadmin role in that sql sever.

Now so far have seen how to implement error handling in sql code, now we see when it become crucial to handle error in updating or not updating data in other table.

Think of situation like when we need to update or insert data into multiple tables; and we want either all data will get inserted or none, we don’t want partial insertion.

To handle such situation we use transaction, in following example i am trying to update two tables "tbOrderItem" and "tbStock", If anything goes wrong, then no table will be updated and we catch the exact error details.

      
BEGIN TRY  
    BEGIN TRANSACTION;  
    
	UPDATE [dbo].[tbOrderItem]
	   SET [orderId] = 1, [productId] =1, 
		   [quantity] = 120,[unitPrice] = 10.25
	 WHERE oitemId =2
	 	 
	 UPDATE [dbo].[tbStock]
	   SET [quantity] = 5 ,[price] = 10.25,      
		   [updatedOn] = getdate()
	  WHERE StockId=1
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
	 ROLLBACK TRANSACTION;  
	 SELECT  
        ERROR_NUMBER() AS ErrorNumber,  
        ERROR_SEVERITY() AS ErrorSeverity,  
        ERROR_STATE() AS ErrorState,  
        ERROR_PROCEDURE() AS ErrorProcedure,  
        ERROR_LINE() AS ErrorLine,  
        ERROR_MESSAGE() AS ErrorMessage;    
END CATCH;  
GO  

This was an example of how you can use error handling with transaction.

You also may be interested in following tutorial

• SQL Server Transaction Example
• Improve SQL Query Performance in MS SQL Server Database