从 CATCH 块返回输出错误

Question

我有一个存储过程，它使用 try/catch 块执行一个简单的插入事务。

如果事务成功，OUTPUT将返回插入的记录 ID。如果发生错误，我想从 try/catch 块返回内容。我的当前代码将在发生错误的情况下返回空白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>

有没有办法在执行后将错误从存储过程返回到 ColdFusion？

Answer 1

尝试cfcatch.detail来了解这些错误结果：

我提前道歉。我确实在没有测试/编译(但我想你明白了(和关于如何处理错误的 CF 文档的情况下一起破解了这个。看看cfcatch.detail带来了什么：

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>