frequent frequent - 18 days ago 4
MySQL Question

Can I call a stored procedure in a cfloop and output dynamic out-parameters in Coldfusion?

Last question for tonight, still using Coldfusion8 and MySQL.

I have a table with products, each with Price A, B and C. I need to retrieve the min and max values for A,B,C across all prices (A_min, A_max, B_min, B_max, C_min, C_max)

I thought I would create a stored procedure and loop through A,B,C like so:

<cfloop list="A,B,C" index="what" delimiters=",">
<cfstoredproc procedure="proc_search_select_minmax" datasource="dtb">
<cfprocparam type="in" value="#what#" cfsqltype="cf_sql_varchar" maxlength="15">
<cfprocparam type="in" value="#variables.xxx#" cfsqltype="cf_sql_varchar" maxlength="13">
<cfprocparam type="in" value="#variables.yyy#" cfsqltype="cf_sql_varchar" maxlength="13">
<cfprocparam type="in" value="#variables.zzz#" cfsqltype="cf_sql_text" maxlength="4">
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_min">
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_max">
</cfstoredproc>
</cfloop>


So the idea was to run this three times for A,B and C and get variables A_min, A_max, B_min... out of the loop.

But I'm having trouble with my out-parameters, which inside MySQL, I'm declaring like:

CREATE ... PROCEDURE `proc_search_select_minmax`(..., OUT `outputMin` DECIMAL(12,2), OUT `outputMax` DECIMAL(12,2))

....
SET outputMin = min(what);
SET outputMax = max(what);


Coldfusion error says:

Error Executing Database Query
@
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_min">
<cfprocparam type="out" cfsqltype="cf_sql_decimal" variable="#what#_max">


Questions:

Do I have to give my out parameters the same name as inside MySQL or is the correct order enough?

More importantly, can I set output variables dynamically like this? If not, are there any other ways except calling the stored procedure three separate times?

Answer

I never liked the variable return way of doing this. Useful but often difficult (depends on order etc).

I have 2 suggestions for you.

First, make the output a data set. In your stored procedure create a temp table (#myMinMax or whatever) with 2 columns minimum and maximum - populate the table with an insert and then select it out returning it as a <cfstoredprocresult..>

Secondly I would probably create a stored proc that does the looping and returns a whole dataset with a "type" column ... so you would end up with a dataset having type (as in A) minimum (as in 10) and maximum (as in 100) ... one row for A, one for B and one for C. A single connection to the datasource could return this dataset for you - avoiding 3 (or more) DB calls.