Problem with output param

[merrillaldrich]

I've been beating my head against this one, and there's probably some simple
problem that I just keep overlooking.

I have a stored proc to perform an insert (SQL Server), and there's an output
parameter that is set to the identity from the new row (scope_identity()).
Standard stuff, working in Query Analyser perfectly.

I then have a CFSTOREDPROC tag set to call that procedure, and to put the
output parameter into a variable. I've done this a few times without incident,
but this time, I get an error that the identity value is not an integer, and if
I output the value to the page, it indeed displays as a series of "square box"
characters rather than the number. I am stumped. Other similar procedures work
with the same design.

I imagine I'm just overlooking some obvious syntax error. Anyone see the
problem?

<cfstoredproc procedure="addLostPropReq" returncode="no"
datasource="YardiTracking" debug="no">
<cfprocparam type="in" dbvarname=@db cfsqltype="cf_sql_varchar"
value="#form.db#">
<cfprocparam type="in" dbvarname=@propcode cfsqltype="cf_sql_char"
value="#form.propcode#">
<cfprocparam type="in" dbvarname=@propname cfsqltype="cf_sql_varchar"
value="#form.propname#">
<cfprocparam type="in" dbvarname=@datelost cfsqltype="cf_sql_timestamp"
value="#form.datelost#">
<cfprocparam type="in" dbvarname=@submittedby cfsqltype="cf_sql_varchar"
value="#form.submittedby#">
<cfprocparam type="in" dbvarname=@notes cfsqltype="cf_sql_varchar"
value="#form.notes#">

<cfprocparam type="out" dbvarname=@reqID cfsqltype="cf_sql_integer"
variable="newreqid">
</cfstoredproc>

<cfquery name="newReq" datasource="YardiTracking">
select * from getLostPropReq(
<cfqueryparam cfsqltype="cf_sql_integer" value="#newreqid#">
)
</cfquery>

and the stored procedure

CREATE PROCEDURE addLostPropReq

@db varchar(20),
@propcode char(8),
@propname varchar(50),
@datelost datetime,
@submittedby varchar(50),
@notes varchar(512),

@reqID int output

AS

set nocount on

declare @err int

insert into lostPropRequests (
db,
propcode,
propname,
datelost,
submittedby,
notes
) values (
@db,
@propcode,
@propname,
@datelost,
@submittedby,
@notes
)

set @reqID = scope_identity()

return 0

[merrillaldrich]

This is the error message:

Error Diagnostic Information
VALUE

Invalid data '



















' for CFSQLTYPE 'CF_SQL_INTEGER'.

The error occurred while processing an element with a general identifier of
(CFQUERYPARAM), occupying document position (35:5) to (35:64).

[MikerRoo]

The dbvarname values need to be in quotes like: dbvarname="@db".

HOWEVER, dbvarname is depricated and CF goes by order only! So if the CF
param order and the SP param order become mismatched, that can cause this.

Finally, are you always inserting into lostPropRequests?
What about duplicates?

You should also definitely initialize @reqID to 0 or a safe value at the top
of the SP.
Also, return non-zero if the scope ID was not set for some reason.

[reenaroy]

try this...
set @reqID =@@identity

[merrillaldrich]

Thank you for the suggestions -- however, I am confident about using
scope_identity(), because I have a great many procedures that use that, that
are all working. It's generally prefered over @@identity in SQL Server 2000 +
because of @@identity's limitations. Note that the SP works perfectly, and
returns the right value, in Query Analyser.

I'll look into the quotation marks thing -- but similarly, I have a whole lot
of these working and none of them have quotation marks there. I'll also
initialize @reqid.

It's really odd. Any other thoughts?

[merrillaldrich]

I seem to have isolated the problem: parameters in cfstoredproc that use
type="cf_sql_timestamp" in this old version of cold fusion will somehow hose
the output parameter. If I use "cf_sql_varchar" instead, the problem magically
disappears. Odd. But now reproducible :-).



This works:

<cfstoredproc procedure="addLostPropReq" returncode="no"
datasource="YardiTracking" debug="no">
<cfprocparam type="in" dbvarname=@db cfsqltype="cf_sql_varchar"
value="#form.db#">
<cfprocparam type="in" dbvarname=@propcode cfsqltype="cf_sql_char"
value="#form.propcode#">
<cfprocparam type="in" dbvarname=@propname cfsqltype="cf_sql_varchar"
value="#form.propname#">

<!--- Following is really a date, but is passed as varchar to work around some
problem with output parameter
in CFSTOREDPROC --->
<cfprocparam type="in" dbvarname=@datelost cfsqltype="cf_sql_varchar"
value="#form.datelost#">

<cfprocparam type="in" dbvarname=@submittedby cfsqltype="cf_sql_varchar"
value="#form.submittedby#">
<cfprocparam type="in" dbvarname=@notes cfsqltype="cf_sql_varchar"
value="#form.notes#">

<cfprocparam type="out" dbvarname=@reqID cfsqltype="cf_sql_integer"
variable="newreqID">
</cfstoredproc>

[MikerRoo]

Good, but you should still quote the dbvarnames in case the code is ever run in later versions of CF.

[merrillaldrich]

Good point, thank you :-)