Using Try ... Catch constructs in SQL Server 2005 (and higher) to manage transaction control in Stored Procedures

Overview

Both InTouch and Application server scripting has at time had problems dealing with the RAISERROR function in SQL Server and has been known to crash a script on occasion. Since SQL Server 2005 Microsoft introduced the Try ... Catch constructs to SQL Server it makes the handling transaction control (BEGIN ... COMMIT or ROLLBACK TRANS) much easier to manage and more stable in Wonderware. A useful approach in using the Catch part would be to capture any errors thrown by a procedure and log them to the same database while returning their location to the querying script from Industrial Appplication Server.

Resolution

This involves building a table to contain error information and a stored procedure for logging the errors and then calling them from all other stored procedures using the Try .. Catch in database application stored procedures. Example code is as follows:

 

1. Create a Table to contain the transaction errors as follows.

CREATE TABLE [dbo].[ErrorLog](
  [ErrorLogID] [bigint] IDENTITY(1,1) NOT NULL,
  [ErrorDate] [datetime] NULL,
  [ErrorNo] [int] NULL,
  [ErrorSeverity] [int] NULL,
  [ErrorState] [int] NULL,
  [ErrorProcedure] [nvarchar](128) NULL,
  [ErrorLine] [int] NULL,
  [ErrorMessage] [nvarchar](4000) NULL
) ON [PRIMARY]
GO

2. Create a SQL Stored Procedure to log any error messages to the table above as follows.

CREATE PROCEDURE usp_LogErrorInfo
-- ======================================================================
-- Author: JBT
-- Date: 3/1/2008
--
-- Description:
-- Procedure to retrieve error information within the scope of the
-- CATCH block of a TRY ... CATCH construct, which uses the following
-- system functions:
--    - ERROR_LINE() returns the line number at which the error occurred.
--    - ERROR_MESSAGE() returns the text of the message that would be
--      returned to the application. The text includes the values supplied
--      for any substitutable parameters, such as lengths, object names,
--      or times.
--    - ERROR_NUMBER() returns the error number.
--    - ERROR_PROCEDURE() returns the name of the stored procedure or
--      trigger in which the error occurred. This function returns NULL if
--      the error did not occur inside a stored procedure or trigger.
--    - ERROR_SEVERITY() returns the severity.
--    - ERROR_STATE() returns the state.
-- ======================================================================
  @ErrorLogID [int] = 0 OUTPUT
AS
SET NOCOUNT ON;

  INSERT INTO [ErrorLog]
   ( [ErrorDate]
    ,[ErrorNo]
    ,[ErrorSeverity]
    ,[ErrorState]
    ,[ErrorProcedure]
    ,[ErrorLine]
    ,[ErrorMessage])
  SELECT GETDATE(),
    ERROR_NUMBER(),
    ERROR_SEVERITY(),
    ERROR_STATE(),
    ERROR_PROCEDURE(),
    ERROR_LINE(),
    ERROR_MESSAGE()

-- Retreives the last record ID
SELECT @ErrorLogID = @@IDENTITY;
GO

3. The following is an example of how the Try ... Catch constructs would be used with the above.

BEGIN TRY
  -- Generate divide-by-zero error.
  SELECT 1/0;
END TRY
BEGIN CATCH
  -- Execute error retrieval routine.
  DECLARE @ErrID int
  EXECUTE usp_LogErrorInfo @ErrID OUT;
  PRINT 'Error logged in ErrorLog table ... ErrorLogID = ' + CAST(@ErrID AS varchar(10))
END CATCH;
GO

SELECT * FROM ErrorLog
GO

4. Building the SQL Stored Procedures to use the error logging above, as follows.

CREATE PROCEDURE [dbo].[usp_TheAppSP]
-- =================================================================
-- Author: JBT
-- Date: 2/8/2008
-- Modified: 3/1/2008
--
-- Description:
--
-- =================================================================
  @RtnMsg varchar(254) OUT,
  @LotNo varchar(50),
  @LotAmt real
AS
BEGIN TRY
  BEGIN TRANSACTION;

  SET @RtnMsg = 'Success'


  -- ... Your stored procedure SQL content goes here


  IF @@TRANCOUNT > 0
  BEGIN
    --PRINT N'A transaction needs to be Committed. ' + CAST (@@TRANCOUNT AS VARCHAR(3));
    COMMIT TRANSACTION;
  END
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
    --PRINT N'A transaction needs to be Rolled back. ' + CAST (@@TRANCOUNT AS VARCHAR(3));
    ROLLBACK TRANSACTION;
  END
  DECLARE @ErrID int
  EXECUTE usp_LogErrorInfo @ErrID OUT;
  SET @RtnMsg = 'ErrorLogID = ' + CAST(@ErrID AS varchar(10)) + ' -:- ' + ERROR_MESSAGE()
END CATCH;
GO

5. The following is an example of how the Stored Procedures would be used.

DECLARE @RC int
DECLARE @RtnMsg varchar(254)

EXECUTE @RC = [usp_TheAppSP] @RtnMsg OUTPUT,'9999-999', 1000

PRINT N'ErrCode = ' + @RtnMsg

IF @RC <> 0
BEGIN
  PRINT 'ReturnCode = ' + CAST(@RC AS CHAR)
  SELECT * FROM ErrorLog
END

Enjoy! ... Bruce Telford.