Introduction
Handling errors in SQL Server became easy with a number of different ways. SQL
Server 2005 introduced the new option that helps to handle the errors
effectively. Sometimes we cannot capture the errors which occurred in the end
user. Even if we want to know the errors which occurred in the end user, we need
to write the code to send it to us. It creates an additional overhead for the
server.
SQL Server 2005 introduced the TRY...CATCH statement
which helps us to handle the errors effectively in the back end. This handling
of the exception can provide additional information about the errors.
TRY...CATCH
The TRY...CATCH statement
works the same as in the programming languages. First it gets executed in the
SQL statement which we have written in the TRY block
and if any error occurs, then it will get executed the CATCHblock.
There are a number of error handling property statements like the following:
ERROR_NUMBER()
ERROR_STATE()
ERROR_SEVERITY()
ERROR_LINE()
ERROR_PROCEDURE()
ERROR_MESSAGE()
|
Normally, SQL Server stores the default error messages which occurred in the execution in the following system table:
SELECT * FROM SYS.MESSAGES
|
But we can create our own error message details with the help of this exception
handling.
Handling the Exception using TRY...CATCH
The below example shows the practical implementation of TRY...CATCH exception
handling technique in Northwinddatabase.
USE
[NorthWind]
GO
IF
OBJECT_ID('dbo.ErrorTracer')
IS NOT
NULL
BEGIN
DROP TABLE dbo.ErrorTracer
PRINT 'Table
dbo.ErrorTracer Dropped'
END
GO
CREATE
TABLE ErrorTracer
(
iErrorID
INT PRIMARY
KEY IDENTITY(1,1),
vErrorNumber
INT,
vErrorState
INT,
vErrorSeverity
INT,
vErrorLine
INT,
vErrorProc
VARCHAR(MAX),
vErrorMsg
VARCHAR(MAX),
vUserName
VARCHAR(MAX),
vHostName
VARCHAR(MAX),
dErrorDate
DATETIME DEFAULT
GETDATE()
)
IF
OBJECT_ID('dbo.ErrorTracer')
IS NOT
NULL
BEGIN
PRINT 'Table
dbo.ErrorTracer Created'
END
GO
IF
OBJECT_ID('Proc_InsertErrorDetails')
IS NOT
NULL
BEGIN
DROP PROCEDURE [dbo].[Proc_InsertErrorDetails]
PRINT 'Procedure
Proc_InsertErrorDetails Dropped'
END
GO
CREATE
PROCEDURE Proc_InsertErrorDetails
AS
/*
Purpose :
Insert the error details occurred in the SQL query
Input :
Insert the details which receives from the TRY...CATCH block
Output :
Insert the details of received errors into the ErrorTracer table
Created By :
Senthilkumar
Created On :
July 17, 2009
*/
BEGIN
SET NOCOUNT ON
SET XACT_ABORT
ON
DECLARE @ErrorNumber VARCHAR(MAX)
DECLARE @ErrorState VARCHAR(MAX)
DECLARE @ErrorSeverity VARCHAR(MAX)
DECLARE @ErrorLine VARCHAR(MAX)
DECLARE @ErrorProc VARCHAR(MAX)
DECLARE @ErrorMesg VARCHAR(MAX)
DECLARE @vUserName VARCHAR(MAX)
DECLARE @vHostName VARCHAR(MAX)
SELECT
@ErrorNumber =
ERROR_NUMBER()
,@ErrorState =
ERROR_STATE()
,@ErrorSeverity =
ERROR_SEVERITY()
,@ErrorLine =
ERROR_LINE()
,@ErrorProc =
ERROR_PROCEDURE()
,@ErrorMesg =
ERROR_MESSAGE()
,@vUserName =
SUSER_SNAME()
,@vHostName =
Host_NAME()
INSERT
INTO ErrorTracer(vErrorNumber,vErrorState,vErrorSeverity,vErrorLine,_
vErrorProc,vErrorMsg,vUserName,vHostName,dErrorDate)
VALUES(@ErrorNumber,@ErrorState,@ErrorSeverity,@ErrorLine,@ErrorProc,_
@ErrorMesg,@vUserName,@vHostName,GETDATE())
END
IF
OBJECT_ID('Proc_InsertErrorDetails')
IS NOT
NULL
BEGIN
PRINT 'Procedure
Proc_InsertErrorDetails Created'
END
GO
IF
OBJECT_ID('Proc_ExceptionHandlingExample') IS NOT
NULL
BEGIN
DROP PROCEDURE [dbo].[Proc_ExceptionHandlingExample]
PRINT 'Procedure
Proc_ExceptionHandlingExample Dropped'
END
GO
CREATE
PROCEDURE Proc_ExceptionHandlingExample
AS
BEGIN
/*
Purpose :
Sample procedure for check the Try...Catch
Output :
It will insert into ErrorTracer table if this
stored procedure throws any error
Created By :
Senthilkumar
Created On :
July 17, 2009
*/
SET NOCOUNT
ON
SET XACT_ABORT
ON
BEGIN TRY
SELECT 15/0
END TRY
BEGIN CATCH
EXEC Proc_InsertErrorDetails
END
CATCH
END
IF
OBJECT_ID('Proc_ExceptionHandlingExample') IS NOT
NULL
BEGIN
PRINT 'Procedure
Proc_ExceptionHandlingExample Created'
END
GO
EXEC
Proc_ExceptionHandlingExample
SELECT
* FROM
ErrorTracer
Steps:
Step 1: Create the custom error table with the name "ErrorTracer".
Step 2: Write the common Stored procedures for handling the
current exception in the name of Proc_InsertErrorDetails which
will help us to insert the error details into step1 created
table.
Step 3: Write a sample procedure and execute with the exception
handling using TRY...CATCH statement.
Whenever an error occurs, it will call the Proc_InsertErrorDetails and
that will insert the error details.
Step 4: Now, check the ErrorTracer table
that will list out the captured error details.
Conclusion
This customized error handling techniques help us to improve the back end error
details effectively. We have given the date and username details in the table
that will help you to find the error details based on the date and username
respectively.