sql-server - ColdFusion 将不需要的输出参数添加到存储过程调用

Question

我从对表进行更新的存储过程中收到以下消息:

过程或函数“updateAttendee”需要未提供的参数“@attendeeAutoID”。

我实际上知道为什么会收到此错误，但我不确定我是否知道如何解决导致错误的原因。

当我分析错误的详细信息和堆栈跟踪时，我可以看到 ColdFusion 似乎正在向存储过程发送一个额外的参数。该参数是一个输出参数。存储过程的参数列表中没有列出输出参数。这是存储过程的代码:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[updateAttendee] 

  @attendeeFName varchar(25)
, @attendeePreferredName varchar(25)
, @attendeeLName varchar(25)
, @attendeePhone varchar(25)
, @attendeeSecondaryPhone varchar(15) = NULL
, @attendeePrimaryEmail varchar(100)
, @attendeeSecondaryEmail varchar(100) = NULL
, @attendeeRank varchar(50)
, @attendeeTitle varchar(100)
, @attendeeOrganization varchar(100)
, @attendeeRequirements varchar(500) = NULL
, @attendeeCountryOfCitizenship varchar(50)
, @attendeeAutoID int
AS
BEGIN

SET NOCOUNT ON;

UPDATE        dbo.conferenceAttendees
SET           attendeeFName = @attendeeFName
        , attendeePreferredName = @attendeePreferredName
        , attendeeLName = @attendeeLName
        , attendeePhone = @attendeePhone
        , attendeeSecondaryPhone = @attendeeSecondaryPhone
        , attendeePrimaryEmail = @attendeePrimaryEmail
        , attendeeSecondaryEmail = @attendeeSecondaryEmail
        , attendeeRank = @attendeeRank
        , attendeeTitle = @attendeeTitle
        , attendeeOrganization = @attendeeOrganization
        , attendeeRequirements = @attendeeRequirements
        , attendeeCountryOfCitizenship = @attendeeCountryOfCitizenship

WHERE       attendeeAutoID = @attendeeAutoID

END

ColdFusion 存储过程调用如下所示:

<cffunction name="updateAttendee" returntype="void" output="No">
<cfargument name="fieldValues" required="yes" type="struct" />
<cfstoredproc procedure="[dbo].[updateAttendee]" debug="yes" returncode="yes" datasource="#this.dsn#" username="#this.userName#" password="#this.password#">
    <cfprocparam type="in" dbvarname="@attendeeFName"                   cfsqltype="cf_sql_varchar"      maxlength="25" value="#arguments.fieldValues.attendeeFName#">
    <cfprocparam type="in" dbvarname="@attendeePreferredName"           cfsqltype="cf_sql_varchar"      maxlength="25" value="#arguments.fieldValues.attendeePreferredName#">
    <cfprocparam type="in" dbvarname="@attendeeLName"                   cfsqltype="cf_sql_varchar"      maxlength="25" value="#arguments.fieldValues.attendeeLName#">
    <cfprocparam type="in" dbvarname="@attendeePhone"                   cfsqltype="cf_sql_varchar"      maxlength="30" value="#arguments.fieldValues.attendeePhone#">
    <cfprocparam type="in" dbvarname="@attendeeSecondaryPhone"          cfsqltype="cf_sql_varchar"      maxlength="30" value="#arguments.fieldValues.attendeeSecondaryPhone#">
    <cfprocparam type="in" dbvarname="@attendeePrimaryEmail"            cfsqltype="cf_sql_varchar"      maxlength="100" value="#arguments.fieldValues.attendeePrimaryEmail#">
    <cfprocparam type="in" dbvarname="@attendeeSecondaryEmail"          cfsqltype="cf_sql_varchar"      maxlength="100" value="#arguments.fieldValues.attendeeSecondaryEmail#">
    <cfprocparam type="in" dbvarname="@attendeeRank"                    cfsqltype="cf_sql_varchar"      maxlength="50" value="#arguments.fieldValues.attendeeRank#">
    <cfprocparam type="in" dbvarname="@attendeeTitle"                   cfsqltype="cf_sql_varchar"      maxlength="100" value="#arguments.fieldValues.attendeeTitle#">
    <cfprocparam type="in" dbvarname="@attendeeOrganization"            cfsqltype="cf_sql_varchar"      maxlength="100" value="#arguments.fieldValues.attendeeOrganization#">
    <cfprocparam type="in" dbvarname="@attendeeRequirements"            cfsqltype="cf_sql_varchar"      maxlength="500" value="#arguments.fieldValues.attendeeRequirements#">
    <cfif arguments.fieldValues.attendeeCountryOfCitizenship EQ "">
        <cfprocparam type="in" dbvarname="@attendeeCountryOfCitizenship"    cfsqltype="cf_sql_varchar"      maxlength="50" value="#arguments.fieldValues.otherCountryOfCitizenship#">
        <cfelse>
        <cfprocparam type="in" dbvarname="@attendeeCountryOfCitizenship"    cfsqltype="cf_sql_varchar"      maxlength="50" value="#arguments.fieldValues.attendeeCountryOfCitizenship#">
    </cfif>
    <cfprocparam type="in" dbvarname="@attendeAutoID"                   cfsqltype="cf_sql_integer"      maxlength="5" value="#arguments.fieldValues.attendeeAutoID#">

</cfstoredproc>

ColdFusion 正在像这样进行 SQL 调用:

{ (param 1) = call [dbo].[updateAttendee](@attendeeFName = (param 2) 
, @attendeePreferredName = (param 3) 
, @attendeeLName = (param 4) 
, @attendeePhone = (param 5) 
, @attendeeSecondaryPhone = (param 6) 
, @attendeePrimaryEmail = (param 7) 
, @attendeeSecondaryEmail = (param 8) 
, @attendeeRank = (param 9) 
, @attendeeTitle = (param 10) 
, @attendeeOrganization = (param 11) 
, @attendeeRequirements = (param 12) 
, @attendeeCountryOfCitizenship = (param 13) 
, @attendeAutoID = (param 14) )} 

生成并发送到存储过程的参数如下所示:

 (param 1) = [type='OUT', sqltype='CF_SQL_INTEGER'] 
, (param 2) = [type='IN', class='java.lang.String', value='qwer', sqltype='cf_sql_varchar'] 
, (param 3) = [type='IN', class='java.lang.String', value='qwer', sqltype='cf_sql_varchar'] 
, (param 4) = [type='IN', class='java.lang.String', value='qwer', sqltype='cf_sql_varchar'] 
, (param 5) = [type='IN', class='java.lang.String', value='232-232-2323', sqltype='cf_sql_varchar'] 
, (param 6) = [type='IN', class='java.lang.String', value='', sqltype='cf_sql_varchar'] 
, (param 7) = [type='IN', class='java.lang.String', value='qwer@ewr.klk', sqltype='cf_sql_varchar'] 
, (param 8) = [type='IN', class='java.lang.String', value='', sqltype='cf_sql_varchar'] 
, (param 9) = [type='IN', class='java.lang.String', value='qwe', sqltype='cf_sql_varchar'] 
, (param 10) = [type='IN', class='java.lang.String', value='qwer', sqltype='cf_sql_varchar'] 
, (param 11) = [type='IN', class='java.lang.String', value='qwer', sqltype='cf_sql_varchar'] 
, (param 12) = [type='IN', class='java.lang.String', value='ghj', sqltype='cf_sql_varchar'] 
, (param 13) = [type='IN', class='java.lang.String', value='United States', sqltype='cf_sql_varchar'] 
, (param 14) = [type='IN', class='java.lang.Integer', value='685', sqltype='cf_sql_integer'] 

如您所见，Param 1 作为 OUTPUT 参数生成，导致参数列表不同步。我不是要求添加此参数。 CF 中的某些东西似乎正在做这件事。当我在服务器上的 SQL 窗口中执行存储过程时，它运行良好。

我的问题是，如何防止 CF 添加此额外参数？

Answer 1

从评论中提升

您看到的附加输出参数是由于您包含并启用了 returncode <cfstoredproc> 的参数在这行代码中标记。

<cfstoredproc procedure="[dbo].[updateAttendee]" debug="yes" returncode="yes" datasource="#this.dsn#" username="#this.userName#" password="#this.password#">

通过添加 returncode="yes" ColdFusion 正在请求额外的输出变量来捕获存储过程调用的退出状态。

来自docs :

returnCode - Optional

Default value = no

Description:

• yes: populates cfstoredproc.statusCode with status code returned by the stored procedure.
• no

因此您可以从 <cfstoredproc> 中删除该属性标记或将其设置为 no或者保持原样并在存储过程代码中设置适当的退出状态。