The formal parameter "@CCNumber" was not declared as an OUTPUT parameter

[athanasiusrc]

This stored procedure was working until last week and I can't remember changing any of the input / output variables so I'm not sure why I'm getting the error message I am getting. I have counted and recounted all the parameters in CF to make sure none are missing but SQL still thinks I'm missing something. Can someone take a look at the code and see why SQL is counting 53 parameters when there are really 54?

The start of the SP:

ALTER PROCEDURE [dbo].[pr_EnterWebOrder]
@user_Fk int,
@coupon_Fk int,
@GiftCertificate varchar(200),
@GCAmount money,
@OrderNotes varchar(200),
@PaymentMethod varchar(10),
@CreditCard_Fk int,
@BillTitle varchar(10),
@BillFirstName varchar(50),
@BillLastName varchar(50),
@BillMiddleName varchar(50),
@BillCompanyName varchar(50),
@BillAddress varchar(2000),
@BillCity varchar(50),
@BillState_Fk int,
@BillZip varchar(20),
@BillProvince varchar(50),
@BillCountry_Fk int,
@BillPhone varchar(50),
@BillExt varchar(8),
@BillEmail varchar(100),
@ShipTitle varchar(10),
@ShipFirstName varchar(50),
@ShipLastName varchar(50),
@ShipMiddleName varchar(50),
@ShipCompanyName varchar(50),
@ShipAddress varchar(2000),
@ShipCity varchar(50),
@ShipState_Fk int,
@ShipZip varchar(20),
@ShipProvince varchar(50),
@ShipCountry_Fk int,
@ShipPhone varchar(50),
@ShipExt varchar(8),
@ShipEmail varchar(100),
@OrderEditor_Fk int,
@AdminUUID char(35),
@CreditAccount int,
@TransactionNumber varchar(20),
@AffiliateCode varchar(50),
@Register int,
@OrderSource_Pk int,
@CheckoutCharity int,
@AuthCode varchar(6),
@AffiliateLinkType int,
@CreditReceived money,
@CashReceived money,
@PONumber varchar(20),
@PromisedDate datetime,
@ShipDate datetime,
@CheckNumber varchar(20),
@CCNumber varchar(200),
@CCExpDate int,
@Order_Pk int OUTPUT,
@OrderNumber varchar(20) OUTPUT

The CF code calling it:


The error message in SQL. Notice it only thinks that there are 53 parameters when there are 54:

SQLSTATE HY000
SQL {call pr_EnterWebOrder( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) , (param 13) , (param 14) , (param 15) , (param 16) , (param 17) , (param 18) , (param 19) , (param 20) , (param 21) , (param 22) , (param 23) , (param 24) , (param 25) , (param 26) , (param 27) , (param 28) , (param 29) , (param 30) , (param 31) , (param 32) , (param 33) , (param 34) , (param 35) , (param 36) , (param 37) , (param 38) , (param 39) , (param 40) , (param 41) , (param 42) , (param 43) , (param 44) , (param 45) , (param 46) , (param 47) , (param 48) , (param 49) , (param 50) , (param 51) , (param 52) , (param 53) )}

[Macromedia][SQLServer JDBC Driver][SQLServer]The formal parameter "@CCNumber" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

[athanasiusrc · Accepted solution]

Found the answer. CF reports the wrong error when you submit an empty value for a DATE cfsqltype in a cfprocparam tag. Instead of telling you the data types don't match, it seems that it deletes that procparam from the code.