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

Question

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?

Do you get the same error if you remove #what# from the variable? You might not be able to have a CF variable inside this CF Tag — Brian Hoover, Jun 12 '12 at 20:10
mh. Same error. I read [this](http://www.bennadel.com/blog/152-Dynamic-ColdFusion-Variables-Via-Quoted-Naming.htm?&_=0.7007155815319436#comments_40124) and thought maybe I could use this inside a stored-proc, too — frequent, Jun 12 '12 at 20:12

score 2 · Accepted Answer · edited Jun 12 '12 at 22:20

2

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.

edited Jun 12 '12 at 22:20

Leigh

28,765
10
55
103

answered Jun 12 '12 at 22:02

Mark A Kruger

7,183
20
21

ok. Good tips. So I'm looping inside MySQL? Never done that. I'll both suggestions. Thanks so far! – frequent Jun 12 '12 at 22:29
Agree on type="out" variables. Never succeeded in getting these to work! – frequent Jun 12 '12 at 22:32
did just that. I have a small error left in "my SQL", so if you want to have a look: http://stackoverflow.com/questions/11062812/how-to-return-a-mysql-temporary-table-and-not-its-individual-entries-as-resultse – frequent Jun 16 '12 at 11:01

Evik James · Answer 2 · 2012-06-12T20:23:56.343

0

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.

edited Jun 12 '12 at 20:23

answered Jun 12 '12 at 20:17

Evik James

10,335
18
71
122

@Leigh, I corrected my answer 10 seconds before you got me. :) – Evik James Jun 12 '12 at 20:24
let me give MySQL Debugger another run. Must have missed something. – frequent Jun 12 '12 at 20:28
@EvikJames - Yeah, I saw it .. just *after* I hit submit ;-) (Edit: I tried to delete the then redundant correction before you saw it, but I am too slow :) – Leigh Jun 12 '12 at 20:34

score 0 · Answer 3 · answered Jun 14 '12 at 07:39

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

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

3 Answers3

Linked