How to handle errors in ColdFusion stored procedure results?

Question

I use ColdFusion to call stored procedure to either insert or update users data. These two transactions are separated in two procedures. My SQL code should return row-count 1 or 0 depends if transaction was successful or not. If transaction failed I'm wondering what is the best way to handle errors in that case? Both cfstoredproc are wraped in try/catch block but in case when error occured in SQL procedure my Count variable in result set will return 0 and try/catch won't register error returned from the procedure. Here is my code example:

<cftry>
    <cfif trim(arguments.process) EQ "Insert">
        <cfstoredproc procedure="InsertRec" datasource="#dsn#">
            <cfprocparam dbvarname="@Status" value="#trim(arguments.status)#" cfsqltype="cf_sql_bit" />
            <cfprocparam dbvarname="@Code" value="#trim(arguments.frmcode)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.code))#" />
            <cfprocparam dbvarname="@Name" value="#trim(arguments.name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.name))#" />
            <cfprocresult name="Result"/>
        </cfstoredproc>
    <cfelse>
        <cfstoredproc procedure="UpdateRec" datasource="#dsn#">
            <cfprocparam dbvarname="@Status" value="#trim(arguments._status)#" cfsqltype="cf_sql_bit" />
            <cfprocparam dbvarname="@Code" value="#trim(arguments.code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.code))#" />
            <cfprocparam dbvarname="@Name" value="#trim(arguments.name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.name))#" />
            <cfprocresult name="Result"/>
        </cfstoredproc>
    </cfif>

    <cfset local.fnResults = {
         status : "200", 
         message : "Record successully saved!", 
         recCount : Result.Count
    }>

    <cfcatch type="any">
         <cfset local.fnResults = {
             error : cfcatch, <!--- I use this just for testing purpose. --->
             status : "400",
             message : "Error! Please contact your administrator."
         }>
    </cfcatch>
</cftry>

Code above returns Count column/variable as I mentioned already from Result set. If process sucesfully executed user will be notified with the message. If something is wrong I would like to send them a message that is in my catch block. Here is SQL code:

CREATE PROCEDURE [dbo].[InsertRec] 
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN TRY
        BEGIN
            INSERT INTO dbo.Books(
                Status,Name,Code
            )
            VALUES(
                @Status,@Name,@Code
            )
            SELECT @@ROWCOUNT AS Count;
        END
    END TRY
    BEGIN CATCH
        SELECT 
            @@ROWCOUNT AS Count, 
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            CURRENT_TIMESTAMP AS DateTime
    END CATCH

I only showed Insert SQL code since Update procedure is the same. When I use I would see this message if something went wrong. This is just and example when I tried to insert primary key that already exist on purpose:

COUNT   
0
DATETIME        
2018-08-24 07:00:01.58
ERRORLINE   
16
ERRORMESSAGE    
Violation of PRIMARY KEY constraint 'PK_Books'. Cannot insert duplicate key in object 'dbo.Books'. The duplicate key value is (44).
ERRORNUMBER 
2627    
ERRORPROCEDURE
InsertRec

ColdFusion is not catching this error. Is there a way to catch this error when I use ColdFusion to call stored procedure in result set?

If you're already handling errors within a try/catch inside the SQL, it won't throw an error in CF, because the error was already handled. Just check the value of `count`. If it is 0, you know the statement failed. — SOS, Aug 24 '18 at 14:23
... and the additional columns will be available for use in any error handling. (Though you shouldn't normally expose users to detailed error information. Just a summary like "cannot do X. Please try again or do Y instead") — SOS, Aug 24 '18 at 14:37
You aren't sending an error to ColdFusion. SQL catches the error before it ever goes to CF and the sproc completes. You also aren't setting an OUTPUT variable for the stored procedure. Also, when you error, just set `Count` to `0`, since you know you didn't insert any rows anyway. — Shawn, Aug 24 '18 at 19:51
And rather than have 2 sprocs to maintain, you can just use a SQL `MERGE` statement that will do what you need. — Shawn, Aug 24 '18 at 19:53
@Shawn There is more than enough reasons why I do not use MERGE. Thanks for suggestion. — espresso_coffee, Aug 24 '18 at 19:54
Just out of curiosity, what are those reasons? That's essentially what you are doing with two different sprocs, unless you have complex logic based on inserting or updating. And if that's the case, your code might be trying to do too much. — Shawn, Aug 24 '18 at 19:59
Here is the all conversation about MERGE: https://dba.stackexchange.com/questions/215585/does-merge-prevent-deadlocks-and-server-blocking. Few things I have state wide system with high volume of transactions. MERGE is well known about slow performance and not being able to prevent concurrency issues. Separating Insert and Update reduces a risk of concurrency/PK violation as well as increasing performance of the system overall. — espresso_coffee, Aug 24 '18 at 20:03

score 4 · Accepted Answer · answered Aug 29 '18 at 00:01

As @Ageax mentions, you're handing the error in your stored procedure, so from CF's point of view, the stored procedure executed correctly.

I tend to use a standard message packet for all calls to stored procedures when they do not return a record set. Add two local variables to your procs and update as needed:

DECLARE @SUCCESS BIT = 1;
DECLARE @MSG VARCHAR(50) = 'Some default success message.'

Update those values in your CATCH statement:

BEGIN CATCH
    SET @SUCCESS = 0;
    SET @MSG = 'There was a problem ...';
    SELECT 
        @SUCCESS as success,
        @MSG as message,
        @@ROWCOUNT AS Count, 
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        CURRENT_TIMESTAMP AS DateTime
END CATCH

But also return these values after the CATCH so that the proc always returns a status.

SELECT 
    @SUCCESS as success,
    @MSG as message

This will give you a user-friendly message, as well as the actual SQL error (when it happens) that can be logged as needed.

How to handle errors in ColdFusion stored procedure results?

1 Answers1