ColdFusion adds unwanted output parameter to a stored procedure call

Question

I am getting the following message from a stored procedure that does an update to a table:

Procedure or function 'updateAttendee' expects parameter '@attendeeAutoID', which was not supplied.

I actually know why I'm getting this error but I'm not sure I know how to fix what is causing it.

When I analyze the details and stack trace of the error, I can see that ColdFusion seems to be sending an extra parameter to the stored procedure. That parameter is an OUTPUT parameter. There are no output parameters listed in the parameter list in the stored procedure. This is the code for the stored procedure:

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

The ColdFusion stored procedure call looks like this:

<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 is making the SQL call like this:

{ (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) )} 

The parameters that are generated and sent to the stored procedure look like this:

 (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'] 

As you can see, Param 1 is generated as an OUTPUT parameter and is causing the parameter list to be out of sync. I'm not asking for this parameter to be added. Something in CF seems to be doing it. When i execute the stored procedure in a SQL window on the server it works perfectly.

My question is, How do I prevent CF from adding this extra parameter?

Answer 1

Promoted from the comments

The additional output parameter that you are seeing is due to the fact that you have included and enabled the returncode parameter of the <cfstoredproc> tag in this line of code.

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

By adding returncode="yes" ColdFusion is requesting that additional output variable to capture the exit status of the stored procedure call.

From the docs:

returnCode - Optional

Default value = no

Description:

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

So you can either remove that attribute from the <cfstoredproc> tag or set it to no or keep it as is and set a proper exit status within your stored procedure code.