Passing Parameters to Stored Procedures from Cold Fusion 2

[jackied]

From experience and other threads I could find on the subject,
Thread232-137754
Thread232-241900

It seems that you have to pass parameters in the correct order and that you have to pass all parameters explicitly, although some were declared as optional in SQL server.

I have a SQL server stored procedure with approx. 30 input parameters, all of which were optional and I only wanted to pass in 4.

In the end I had to explicitly pass in default values for the other 26 (all in the correct order).
I was getting these errors:
Error 8114 Error converting data type %ls to %ls.
and
Error 8162 Formal parameter '%.*ls' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Does anybody know? Can you call stored procedures from Cold Fusion without passing every single parameter explicitly?

I have tried it in MX as well and still had to pass all parameters explicitly in the correct order.


Thanks,
Jackie

 

[pcorreia]

My understanding, based on testing of ColdFusion 4.5 with SQL Server 7 (and not contradicted by anything I have read so far regarding other versions), is that regardless of whether you state the names of the variables you are passing to the stored procedure, they are always passed by order. So, if you have 30 optional parameters to a stored procedure, and you want to pass parameters 1, 2, 3, and 7, you must pass parameters 1-7, with 4, 5, and 6 being "null". You do not have to pass all 30 parameters. If you wanted to pass parameters 1 and 30, you would have to pass all 30, with 2-29 passed as null. Of course, depending on how you are coding your page, it may be easier just to always pass all 30 parameters and decide at runtime which should be null.

Anyone have any experience to the contrary?

 

[SidianConsulting]

You could also re-order the variables in the stored procedure so that all the NULLable ones are at the end of your assignment block.

-Tek

 

[jackied]

Hi Tek and pcorreia

Thanks, I have re-ordered my parameters so that the output parameters are first, followed by those most likely to be passed, which is saving me quite a bit of parameter passing.

However the paramaters passed vary as this stored proc is called from more than one place.

Perhaps I'll write a number of stored procedures to be called from the different pages which each accept a few parameters, and call my main stored proc from there. A bit of a long way round, but I want to maintain the code in the main stored proc in only one place.

I wonder if Macromedia has any plans to improve this in future versions? ...

 

[codr]

What I sometimes do in those cases is test if the variable isDefined and pass in Null="yes" in the stored proc param if that field is left blank in the form.

<--- example --->
<CFIF isDefined(&quot;form.age&quot>
<CFPROCPARAM TYPE=&quot;IN&quot; CFSQLTYPE=CF_SQL_INTEGER
VALUE=&quot;#form.age#&quot; DBVARNAME=@param1>
<CFELSE>
<CFPROCPARAM TYPE=&quot;IN&quot; CFSQLTYPE=CF_SQL_INTEGER
DBVARNAME=@param1 null=&quot;yes&quot;>
</CFIF>

<--- (bear with me if the syntax isn't exactly perfect. I did this from memory since I'm away from my work computer that has CF Studio installed on it ) -->

 

[codr]

One more thing about my previous message... If you're testing a text field instead of a radio button or checkbox, it WILL be defined so you don't have do the <CFIF></CFIF>. I believe it will just pass in an empty string.