Skip to main content
Inspiring
February 22, 2007
Question

No Resulsts from CFC calling MSSQL Procedure

  • February 22, 2007
  • 3 replies
  • 490 views
I'm calling a stored procedure within a CFC. I'm getting this error:

Procedure 'sp_AddCustomer' expects parameter '@newKey', which was not supplied.

<cfstoredproc datasource="#ARGUMENTS.dsn#" procedure="sp_AddCustomer">
<cfprocparam type="in" value="#FORM.donorid#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.email#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.homephone#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.workphone#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.cellphone#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.lastname#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#FORM.firstname#" cfsqltype="cf_sql_varchar">
<cfprocresult name="newKey">
</cfstoredproc>

Here is the stored procedure

CREATE PROCEDURE dbo.sp_AddCustomer

@custnum varchar(20)
,@email varchar(50)
,@homephone varchar(30)
,@workphone varchar(30)
,@cellphone varchar(30)
,@lastname varchar(50)
,@firstname varchar(50)
,@newKey int OUTPUT

AS

Insert Into tblcustomer(custnum
,email
,homephone
,workphone
,cellphone
,lastname
,firstname)
Values(@custnum
,@email
,@homephone
,@workphone
,@cellphone
,@lastname
,@firstname)

Select @newKey = scope_identity();


GO

This topic has been closed for replies.

3 replies

Participating Frequently
February 28, 2007
Just add another cfprocparam with type="OUT" and the variable it should have
Inspiring
February 23, 2007
Mark Forsberg wrote:
> Procedure 'sp_AddCustomer' expects parameter '@newKey', which was not
> supplied.

it's telling you the truth. either provide that OUTPUT arg or simply return the
resultset.

Inspiring
February 23, 2007
PaulH,

I modified the sp to this:

CREATE PROCEDURE dbo.sp_AddDonor

@donornum varchar(20)
,@email varchar(50)
,@homephone varchar(30)
,@workphone varchar(30)
,@cellphone varchar(30)
,@lastname varchar(50)
,@firstname varchar(50)

AS

Insert Into tblDonor(donornum
,email
,homephone
,workphone
,cellphone
,lastname
,firstname)
Values(@donornum
,@email
,@homephone
,@workphone
,@cellphone
,@lastname
,@firstname)

Select scope_identity();
GO

It's now working, thanks. I'm curious. How would I have set up the output parameter in the original stored procedure? Thanks.
Inspiring
February 22, 2007
Any help is appreciated. Thanks.