passing variables to cfstoredproc requires them to be in order

[j9]

This is my first time using cfstoredproc and I'm having a bit of difficulty...I tried using the dbvarname attribute to pass my variables into my sql2000 stored procedure, but it returned no records and a status code of 0. But when I rearranged my <cfprocparam>'s so that the order of the variables was consistent with the order in the stored procedure, it worked. I thought that if you used dbvarname, the order didn't matter?????


<cfprocparam type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@yr&quot; value=&quot;2002&quot;>
<cfprocparam type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@semester&quot; value=&quot;3&quot;>
<cfprocparam type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@myid&quot; value=&quot;111111&quot;>
<cfprocresult name=&quot;Highest&quot;>
</cfstoredproc>


Thanks for any insight!

 

[pcorreia]

You and me both, and probably the rest of the CF community. This isn't documented anywhere, but it seems to be a limitation of ColdFusion. I've asked in previous threads whether anyone using CF5 or CFMX (we're still on 4.5) knows whether this has been fixed -- what version are you running?

I guess this doesn't help your problem, but at least you know it's not something in your code. You just have to write your stored procedures in such a way that all the optional parameters are last. Also, if you have 3 optional parameters at the end and you want to pass #2, you also have to pass #1 (as NULL) or #2 gets interpreted as #1. A BIG pain.

 

[j9]

Thanks. I'm running CF 5. Actually, I got a response from a CF user's group--apparently, there is documentation that mentions that the cfprocparam tag ignores the dbvarname attribute for all drivers and that ColdFusion 5 used it for Sybase, Oracle, and Informix native drivers. I haven't looked at the documentation, but it is located at:
/cfdocs/Migrating_ColdFusion_5_Applications/contents.htm