Cfstoredproc is returning no records when I add an optional parameter

[DannyGoodisman]

I have a stored procedure proc_ADR_get_all_processes that has an optional parameter @iD_process. When no id_process is passed in, all processes are returned, but the user can search for a particular process. This works in Toad for SQL Server: "exec proc_ADR_get_all_processes" returns all records and "exec proc_ADR_get_all_processes @iD_process=1650" returns just that one.

However, when I try to move it back to my application, it doesn't work.

<cfstoredproc procedure="proc_ADR_get_all_processes" datasource="#request.dataSource#"><cfprocresult name="processes"></cfstoredproc>

returns all results as it should.

But

<cfstoredproc procedure="proc_ADR_get_all_processes" datasource="#request.dataSource#">
<cfprocparam dbvarname="@id_process" cfsqltype="cf_sql_int" value="1650" type="in" >

<cfprocresult name="processes">

</cfstoredproc>

returns no results, not the one it should (for testing, I hard-coded this value, naturally it will be dynamic).

Has anyone seen this before? Is there something I should look out for? Your response will be much appreciated.

Danny Goodisman

[Adam Cameron.]

What happens if yuo get rid of the dbvarname attribute from the <cfprocparam> tag?  As per the docs, that attribute is no longer supported, and you should not use it: you must use positional parameters, unfortunately.

--

Adam

[Dan_Bracuk]

If you want to use named parameters, you can always use cfquery instead of cfstoredproc.