Free Web Development Online Tutorials, Learn to Code
sql server error handling best practices, sql server stored procedure error handling best practices

Sql server error handling best practices

SQL Server Error Handling

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

We learn 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

SQL Error Handling using TRY CATCH

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

sql error message display

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

Capture SQL error in SQL Table

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

How to find “Error log file location” on your SQL Server

error log file in sql database
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


Comment
Name
Email
Website
Subscribe