Here you learn how to handle error or exception in sql code and log them properly so developer can fix them soon.
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:
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
This will tell us about internal number of the error
Returns the information about the source
tell you where the error is, very helpful
Returns the name of the stored procedure or function
this will get the error details
Here is how Sql insert error handling can be done,
also see how Sql error message displayed using
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
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
We also can get error log information by executing SP_READERRORLOG ,but to execute this sp user need to have securityadmin role