My Adventures using eConnect Stored Procedures – Part 2, Error Handling

I’m a big believer in doing things in a consistent fashion, be it formatting SQL code or loading the dishwasher.  To that end, I like to handle errors consistently in my applications and that means using C#’s standard method using Try/Catch. 

The eConnect stored procedures don’t raise an error if one occurs but rather, as mentioned in Part 1, pass back error information in two output parameters, @O_iErrorState, an integer and @oErrString a 255 character varchar. 

Now unfortunately, neither of these variables on their own provide much interesting information.  @O_iErrorState returns a single integer representing an error.  @oErrString sends a list of integers, separated by spaces, if more than one error is occurs.  So where do I get the text that corresponds to the numbers?

The answer is in the taErrorCode table in the DYNAMICS database.  The error codes returned by the eConnect stored procedures correspond to the values in the ErrorCode column.  Using that you can return the stored procedure the error code relates to from the SourceProc column and the error description from the ErrorDesc column.

So that solves the first part of my problem, which is how to retrieve readable text from the error codes, but how do I then go and throw an exception with all of the error information instead of relying on the output parameters?  The first step is to consolidate the information for one or more errors into a single string.  For this I created a user defined scalar function that takes an error code list, as returned by the @oErrString output parameter and returns a single string as follows (best read by cutting and pasting into Management Studio):

CREATE FUNCTION [dbo].[fnvGeteConnectErrorMessage]
(
    @eConnectErrorString nvarchar(255)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @ErrorString nvarchar(255)
    DECLARE @ErrorMessage nvarchar(max)
    DECLARE @DelimiterIndex int
    DECLARE @CurrentErrorCode nvarchar(10)
    DECLARE @CRLF nvarchar(2)

--Initialize variables
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @ErrorString = @eConnectErrorString
    SET @ErrorMessage = 'The following error(s) were returned:' + @CRLF + @CRLF
    SET @ErrorString = LTRIM(RTRIM(@ErrorString))

--If no errors then return empty string
    IF LEN(@ErrorString) = 0
    BEGIN
        RETURN N''
    END

--Find first delimiter
    SET @DelimiterIndex = CHARINDEX(' ', @ErrorString, 0)
    IF @DelimiterIndex = 0
    BEGIN
--If no delimiter, return entire string
        SET @CurrentErrorCode = @ErrorString
    END
    ELSE
    BEGIN
--If delimiter found, set current error code to first error code in string
        SET @CurrentErrorCode = SUBSTRING(@ErrorString, 1, (@DelimiterIndex - 1))
    END

--Loop until there are no error codes remaining
    WHILE LEN(@CurrentErrorCode) > 0
    BEGIN
--Return error stored procedure and message and append to error message to be returned
        SELECT
                @ErrorMessage = @ErrorMessage + 'Source procedure - ' + ISNULL(SourceProc, '')
                    + @CRLF + 'Error - ' + ISNULL(ErrorDesc, '') + @CRLF + @CRLF
            FROM DYNAMICS.dbo.taErrorCode
            WHERE ErrorCode = @CurrentErrorCode

--Remove current error code from string and get next error code
        SET @ErrorString = RTRIM(LTRIM(SUBSTRING(@ErrorString, (LEN(@CurrentErrorCode) + 1), (LEN(@ErrorString) - LEN(@CurrentErrorCode)))))
        SET @DelimiterIndex = CHARINDEX(' ', @ErrorString, 0)
        IF @DelimiterIndex = 0
        BEGIN
            SET @CurrentErrorCode = @ErrorString
        END
        ELSE
        BEGIN
            SET @CurrentErrorCode = SUBSTRING(@ErrorString, 1, (@DelimiterIndex - 1))
        END
    END

    -- Return the result of the function
    RETURN @ErrorMessage
END

What this function is ultimately doing is taking the space delimited list of error codes, looping through them, retrieving the source procedure and error description, appending it to the overall error message and then returning the whole thing.

Now that I can return a consolidated error string, how do I throw an error?  I handled this by creating a wrapper stored procedure.  This not only allows me to handle errors the way I want, but also have a stored procedure with only the parameters I need in my code, with readable names.  Below is the wrapper stored procedure I created to insert a GL transaction header:

CREATE PROCEDURE [dbo].[spvGLInsertGPGLHeader]
    @BatchNumber char(15)
    ,@JournalEntry int
    ,@JournalReference char(30)
    ,@TransactionDate datetime
AS

    DECLARE @RC int
    DECLARE @ErrorState int
    DECLARE @ErrorString varchar(255)
    DECLARE @ErrorMessage nvarchar(max)

--Execute the taGLTransactionHeaderInsert eConnect stored
--procedure with passed parameters and required defaults.
    EXECUTE @RC = [PHI2].[dbo].[taGLTransactionHeaderInsert]
        @I_vBACHNUMB = @BatchNumber
        ,@I_vJRNENTRY = @JournalEntry
        ,@I_vREFRENCE = @JournalReference
        ,@I_vTRXDATE = @TransactionDate
        ,@I_vTRXTYPE =0
        ,@I_vSERIES = 2
        ,@I_vRequesterTrx = 1
        ,@O_iErrorState = @ErrorState OUTPUT
        ,@oErrString = @ErrorString OUTPUT

--If any errors are returned, get the consolidated error message
--and raise a SQL error.
    IF LEN(@ErrorString) <> 0
    BEGIN
        SET @ErrorMessage = dbo.fnvGeteConnectErrorMessage(@ErrorString)
        RAISERROR (@ErrorMessage, 18, 1)

        RETURN
    END

As you can see, we’re using TSQL’s RAISERROR (doesn’t the missing “E” in the middle bug you a little) to throw an error back to your calling code.  Also note that we have a much cleaner stored procedure to reference from our calling application.

Next up, calling this from an ADO.NET table adaptor in a Dynamics GP .NET add-in.