Error Handling

Error Handling (Exception Handling) is one the of key strategy one should think of.
SQL Server 2005 has introduced sophisticated Error Handling approach with TRY-CATCH block which was not available in prior versions.

Here is the approach which can be used as Best practices.

TRY-CATCH BLOCK

• In SP, we should put all our logic in TRY block and CATCH block can be utilized for throwing an error
• Inside TRY block, we can open a transaction and same can be COMMITed at the end
• CATCH block should be used to ROLLBACK a transaction
• New in-built functions have been created in SQL Server 2005 like ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() which are specially for CATCH block.
• Every TRY block should be ending CATCH block. 1 TRY can have only 1 corresponding CATCH block
• Nesting is allowed means one TRY-CATCH block can be part of outer TRY block.

DESIGN

• One Table needs to be created to capture and store all the errors.
• One master SP needs to be created which captures the log in above table. This SP will be kept under CATCH block of all the SPs
• while using RAISERROR , use WITH LOG. This will log in errorlog also.

DESIGN CODE

CREATE TABLE [ErrorLog](

      [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,

      [ErrorDescription] [nvarchar](max) NOT NULL,

      [ErrorSourceName] [varchar](255) NOT NULL,

      [SysMessagesErrorNumber] [int] NOT NULL,

      [ErrorSourceLineNumber] [smallint] NOT NULL,

      [IsDeleted] [bit] NOT NULL DEFAULT 0,

      [CreatedBy] [int] NOT NULL,

      [CreatedDateTime] [datetime] NOT NULL DEFAULT GETUTCDATE(),

 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED

(

      [ErrorLogID] ASC

)

)

CREATE PROCEDURE [uspThrow_Error]

(

      @userProfileId INT=NULL

)

AS

BEGIN

      -- Return if there is no error information to retrieve.

      IF ERROR_NUMBER() IS NULL 

            RETURN;

      DECLARE

            @errorMessage    NVARCHAR(MAX),

            @errorNumber     INT,

            @errorSeverity   INT,

            @errorState      INT,

            @errorLine       INT,

            @errorProcedure  NVARCHAR(200)

      -- Assign variables to error-handling functions that

      -- capture information for RAISERROR.

      SELECT

            @errorNumber = ERROR_NUMBER(),

            @errorSeverity = ERROR_SEVERITY(),

            @errorState = ERROR_STATE(),

            @errorLine = ERROR_LINE(),

            @errorMessage = ERROR_MESSAGE();

      -- Capturing procedure name along with Schema

      SET @errorProcedure = ISNULL(Library.ufnGet_FullObjectName(ERROR_PROCEDURE(),1),'')

      -- This is some technical error. So log into ErrorLog table

      INSERT INTO Library.ErrorLog

      (

            SysMessagesErrorNumber, ErrorSourceName, ErrorSourceLineNumber, ErrorDescription, CreatedBy, CreatedDateTime

      )

      VALUES

      (

            @errorNumber,@errorProcedure, @errorLine, @errorMessage, ISNULL(@userProfileId,1), GETUTCDATE()

      )

      -- Now Raise an error. This time it will take Error number as 50000. So Front-end will show a generic Error page.

      RAISERROR

      (

            @errorMessage,

            @errorSeverity,

            1,

            @errorProcedure,

            @errorNumber,    -- parameter: original error number.

            @errorSeverity,  -- parameter: original error severity.

            @errorState,     -- parameter: original error state.

            @errorProcedure, -- parameter: original error procedure name.

            @errorLine       -- parameter: original error line number.

      ) WITH LOG;

END

GO

SAMPLE CODE

CREATE PROC SampleProc

AS

BEGIN

      BEGIN TRY

            BEGIN TRAN

                  -- Your code

                  SELECT 1/0

            COMMIT

      END TRY

      BEGIN CATCH

            EXEC [uspThrow_Error]

      END CATCH

END