sql - 如何使用 OUTPUT SQL 2008 返回错误？

Question

你好，我有一个存储过程，它使用 try/catch block 执行简单的插入事务。如果交易成功，OUTPUT 将返回插入的记录 ID。如果发生错误，我想从 try/catch block 中返回内容。在发生错误的情况下，我当前的代码将返回空白 RecID。这是我的代码示例:

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

如果我尝试插入已存在的相同记录，上面的代码将返回以下内容:

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

这是我所期望的，这在 Management Studio 2008 中显示。当我使用 ColdFusion 调用此过程时，我只得到 RecID = ''。这是我如何调用此过程的示例:

<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>

有没有办法在执行后将存储过程的错误返回给CodlFusion？

Answer 1

您的代码实际上与您编写的代码完全一样。它正在捕获错误并在 CATCH block 中输出您的数据。如果您想捕获特定类型的错误并以不同方式处理它，那么您必须指定它。 2627 是您的 Primary Key Constraint Violation 错误，但我不确定您是否想将其视为实际错误。最好先看看你的主键是否存在。

尝试这样的事情:

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
        /* Check if your primary key record exists. */
        IF EXISTS(SELECT 1 FROM dbo.Dictionary WHERE ActionID = @ActionID) 
          /* Instead of ActionID, use whatever your Primary Key is. */
        BEGIN
            SELECT NULL ;  /* This returns NULL if the primary key record exists. */
        END
        ELSE
        /* PK record does not exist, so try to INSERT. */
        BEGIN TRY
            INSERT INTO dbo.Dictionary(
                Status,Name,Code,ActionDt,ActionID
            )
            OUTPUT INSERTED.RecID
            VALUES(
                @Status,@Name,@Code,CURRENT_TIMESTAMP,@ActionID
            );
        END TRY
        BEGIN CATCH /* INSERT errored, so return the data of what it was. */
            SELECT  
                ERROR_PROCEDURE() AS ErrorProcedure,
                ERROR_LINE() AS ErrorLine,
                ERROR_NUMBER() AS ErrorNumber,
                ERROR_MESSAGE() AS ErrorMessage,
                CURRENT_TIMESTAMP AS DateTime
        END CATCH

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9601e1c43d38ef551f23f2c866e50b5d

注意:要捕获特定的错误代码(您必须找到您要查找的错误代码)，请使用类似以下内容的方法:

BEGIN CATCH
  IF ERROR_NUMBER() = 2627  /* This is a PK Violation error */
    /* DO YOUR PK VIOLATION STUFF HERE. */
END CATCH