MSSQL Stored Procedure

[tempestweb]

This is my first attempt at using sotred procedures, and a slight problem has
come up. I'm collecting data to order a widget, and the first thing I want to
do is create an order ID, in this case called "prodID" wit a sotred procedure.
I'm putting dummy values into the fields that do nto allow null:

CREATE PROCEDURE NewProdForm
@custUUID varchar (36),
@pttType varchar(20),
@pttWeight int,
@initDate datetime,
@delDate datetime,
@prodID int = null OUTPUT
AS

-- make a new enpty line in the production table
INSERT INTO production (custUUID, pttType, pttWeight, initDate,delDate)
VALUES (@custUUID,@pttType,@pttWeight,@initDate,@delDate)

-- send back the record number as prodID
SET @prodID = @@IDENTITY
RETURN 1

GO

In the page where I'm calling the stored procedure, I'm getting an error that
the prodID variable doesn't exist.

<CFSTOREDPROC procedure="microwavetelem.NewProdForm"
datasource="microwavetelem" username="microwavetelem" password="#verb#">
<CFPROCPARAM type="IN" dbvarname="@custUUID" value="#FORM.custUUID#"
cfsqltype="CF_SQL_VARCHAR">
<CFPROCPARAM type="IN" dbvarname="@pttType" value="PTT"
cfsqltype="CF_SQL_VARCHAR">
<CFPROCPARAM type="IN" dbvarname="@pttWeight" value="80"
cfsqltype="CF_SQL_INTEGER">
<CFPROCPARAM type="IN" dbvarname="@initDate" value="1/1/1900"
cfsqltype="CF_SQL_TIMESTAMP">
<CFPROCPARAM type="IN" dbvarname="@delDate" value="1/1/1900"
cfsqltype="CF_SQL_TIMESTAMP">
<CFPROCPARAM type="OUT" dbvarname="@prodID" variable="prodID"
cfsqltype="CF_SQL_INTEGER">
<CFPROCRESULT name="rsGetProdID">
</CFSTOREDPROC>

" Element PRODID is undefined in RSGETPRODID."

What did I miss? I know the records are being created, but the ID isn't being
provided to the next step in the process, which is to add that number in a
field in another data table with a #rsGetProdID.prodID# to put the number where
I want it.

[woodfoot]

Try this:

In your stored proc:

change this:

SET @prodID = @@IDENTITY
RETURN 1

to:

SELECT @@IDENTITY as BPFID;
RETURN(1)


See if that works for ya.

[woodfoot]

sorry

to:

SELECT @@IDENTITY as prodID;
RETURN(1)

[woodfoot]

Additionally, I do not believe you need the output parameter:

<CFPROCPARAM type="OUT" dbvarname="@prodID" variable="prodID" cfsqltype="CF_SQL_INTEGER">

I know I've never have had to use this...

[tempestweb]

This was copied from an example in CFMX7 Web Application Construction Kit; the CFPARAM OUT was there, so I used it. The

SELECT @@IDENTITY as prodID;
RETURN(1)

worked. Thank you.