Search code examples
mysqlstored-proceduresdynamiccoldfusionout-parameters

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?

Solution

  • 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.