Can I call a stored procedure in a cfloop and outp

问题:

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?

回答1:

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.

回答2:

In SQL Server, we set our variables with the @ sign in the dbvarname attribute.

<cfprocparam cfsqltype="cf_sql_integer" 
    value="#LOCAL.User_ID#" 
    dbvarname="@User_ID">

Give that a try.

UPDATE ~ I just checked the CF docs, the advice above won't help you

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_14.html

Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion uses JDBC 2.2 and does not support named parameters.

回答3:

I agree with Mark about output parameters. For a single value, you could go either way. But beyond that it is simpler to just return a resultset.

Error Executing Database Query

That said - your code works fine with the sample procedure below in CF8/MySQL5. Based on the partial error message, I suspect the real problem is a syntax error in your procedure. Did you test it directly in your database?

<cfloop>
    <cfstoredproc ...>
        ....
    </cfstoredproc>
</cfloop>
<cfoutput>
    #a_min# #a_max# <br />
    #b_min# #b_max# <br />
    #c_min# #c_max# <br />
</cfoutput>


CREATE PROCEDURE `test`.`proc_search_select_minmax` (
     what varchar(50)
     , xxx varchar(50)
     , yyy varchar(50)
     , zzz varchar(50)
     , OUT outputMin decimal(12,2)
     , OUT outputMax decimal(12,2)
 )
BEGIN

      SET outputMin = 1;
      SET outputMax = 20;
END