Return error with OUTPUT from CATCH block

Question

I have a stored procedure that executes a simple insert transaction with a try/catch block.

If the transaction is successful, OUTPUT will return the inserted record ID. If an error happens, I would like to return content from the try/catch block. My current code will return a blank RecID in the situation where an error occurs. Here is my code example:

CREATE PROCEDURE [dbo].[InsertRecord] 
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN TRY
        BEGIN
            INSERT INTO dbo.Dictionary(
                Status,Name,Code,ActionDt,ActionID
            )
            OUTPUT INSERTED.RecID
            VALUES(
                @Status,@Name,@Code,CURRENT_TIMESTAMP,@ActionID
            );
        END
    END TRY
    BEGIN CATCH 
        SELECT  
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            CURRENT_TIMESTAMP AS DateTime
    END CATCH

Here is what code above will return if I try to insert a record that already exists:

ErrorProcedure  
InsertRecord    
ErrorLine   
16              
ErrorNumber 
2627
ErrorMessage    
Violation of PRIMARY KEY constraint 'PK_Code'.  
Cannot insert duplicate key in object 'dbo.Dictionary'.  
The duplicate key value is (44).
DateTime
2018-08-23 10:46:02.920

This is what I expect and this is showing in Management Studio 2008. When I call this procedure with ColdFusion I just get RecID = ''. Here is example how I call this procedure:

<cftry>
    <cfstoredproc procedure="InsertRecord" datasource="#dsn#">
        <cfprocparam dbvarname="@Status" value="#trim(arguments.frm_status)#" cfsqltype="cf_sql_bit" />
        <cfprocparam dbvarname="@Code" value="#trim(arguments.frm_code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.frm_code))#" />
        <cfprocparam dbvarname="@Name" value="#trim(arguments.frm_name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.frm_name))#" />
        <cfprocparam dbvarname="@ActionID" value="#trim(SESSION.UserID)#" cfsqltype="cf_sql_idstamp" maxlength="50" null="#!len(trim(SESSION.UserID))#" />
        <cfprocresult name="Result"/>
    </cfstoredproc>

    <cfset local.fnResults = {status : "200", RecID : Result.RecID}>

    <cfcatch type="any">
        <cfset local.fnResults = {error:cfcatch,status : "400", class : "alert-danger", message : "Error! Please contact your administrator."}>
    </cfcatch>
</cftry>

Is there a way to return the error from a stored procedure to ColdFusion after execution?

Hi there, Welcome to the site. It seems this question is more about ColdFusion than SQL Server so it might be a better fit for StackOverflow — Tom V
– Tom V, Commented Aug 24, 2018 at 7:30

Sting · Accepted Answer · 2018-08-23 20:30:11Z

1

Try cfcatch.detail for those Error Results:

My apologies in advance. I did hack this together without testing/compiling (but I think you get the idea) and CF documentation on how to handle errors. See what cfcatch.detail brings to the table:

cftry>
  <cfstoredproc procedure="InsertRecord" datasource="#dsn#">
        <cfprocparam dbvarname="@Status" value="#trim(arguments.frm_status)#" cfsqltype="cf_sql_bit" />
        <cfprocparam dbvarname="@Code" value="#trim(arguments.frm_code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.frm_code))#" />
        <cfprocparam dbvarname="@Name" value="#trim(arguments.frm_name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.frm_name))#" />
        <cfprocparam dbvarname="@ActionID" value="#trim(SESSION.UserID)#" cfsqltype="cf_sql_idstamp" maxlength="50" null="#!len(trim(SESSION.UserID))#" />
        <cfprocresult name="Result"/>
    </cfstoredproc>

    <cfset local.fnResults = {status : "200", RecID : Result.RecID}>

<cfcatch type="database">
   <cfoutput>
   <p><b>database error</b><br />
   <ul>
   <li><b>message:</b> #cfcatch.message#
   <li><b>native error code:</b> #cfcatch.nativeerrorcode#
   <li><b>sqlstate:</b> #cfcatch.sqlstate#
   <li><b>detail:</b> #cfcatch.detail#
   </ul>
   </cfoutput>
</cfcatch>
</cftry>

answered Aug 23, 2018 at 20:30

Sting

3523 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Solomon Rutzky Over a year ago

I don't use ColdFusion and have no way to test, but I am guessing that this would require that the O.P. replace the SELECT in the CATCH block with RAISERROR, else it will be seen as a normal result set and not a catchable exception.

Collectives™ on Stack Overflow

Return error with OUTPUT from CATCH block

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related