Catch Sql Server Error Message

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    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

I found this snippet at http://www.codeproject.com/KB/database/ExcepHandling.aspx

Here is the complete page I grabbed for future reference:

Introduction

Handling errors in SQL Server became easy with the number of different ways. SQL Server 2005 has 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 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 CATCH block.

Collapse

BEGIN TRY
// SQL Statements
END TRY
BEGIN CATCH
//Handle the exception details
END CATCH

There are a number of error handling property statements like the following:

Collapse

 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:

Collapse

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 Northwind database.

Collapse

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.