How to loop through stored procedure result in coldfusion

Question

I would like to loop the stored procedure in Coldfusion. I have below code to loop through query.

<cfquery name="rates" dbtype="query">
        select code, rate
  from application.qry.currency
  where code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.origCode#" />
     or code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.toCode#" />
</cfquery>
<cfloop query="rates">
    <cfscript>
            if (code == arguments.origCode) origRate = rate;
            if (code == arguments.toCode) toRate = rate;
        </cfscript>
</cfloop>

Now the query is being replaced by the stored procedure as below and I would like to do the same function as above code does for looping. And inside the stored procedure, I do have the same query as above. Thank you in advance!

<cfstoredproc procedure="usp_get_rates" datasource="#variables.dsn#">
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@origCode" type="in" value="#arguments.origCode#"/>
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@toCode" type="in" value="#arguments.toCode#" />
</cfstoredproc>

Full code for the above:

<cffunction name="convertCurrency" access="public" output="false" returntype="string">
  <cfargument name="amount" required="true" type="numeric" />
<cfargument name="origCode" required="true" type="string" />
<cfargument name="toCode" required="true" type="string" />
<cfscript>
        var theAmount = 0;
        var origRate = 0;
        var toRate = 0;
        var baseVal = 0;
        var newVal = 0;
        if (isNumeric(arguments.amount)) theAmount = arguments.amount;
        if (arguments.origCode == 'USD' && arguments.toCode == 'USD' || theAmount == 0) return theAmount;
        if (not isDefined("application.qry.currency")) initCurrencyRecordset();
    </cfscript>
<cfquery name="rates" dbtype="query">
        select code, rate
  from application.qry.currency
  where code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.origCode#" />
     or code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.toCode#" />
</cfquery>
<cfloop query="rates">
    <cfscript>
            if (code == arguments.origCode) origRate = rate;
            if (code == arguments.toCode) toRate = rate;
        </cfscript>
</cfloop>
<cfscript>
        if (origRate neq 0){
            baseVal = theAmount * (1 / origRate);
            newVal  = baseVal * toRate;
        }
        else {
            baseVal = theAmount * 1;
            newVal  = baseVal * toRate;
        }
        return newVal;
    </cfscript>

  <cffunction name="initCurrencyRecordset" access="private" output="false" returntype="void">
    <!--- <cfquery name="qry" datasource="#variables.dsn#">
        select distinct c.LOCAL_CUR as code, ISNULL(c.LocalConversionToUSD, c.x) as rate
        from SCS_FM.dbo.currency c
        join scs_fm.dbo.currency_code cc
            ON c.local_cur = cc.currency_code
        order by LOCAL_CUR
    </cfquery> --->

    <cfstoredproc procedure="usp_Rates_InitCurrencyRecordset_Qry" datasource="#variables.dsn#">
    <cfprocresult name="qry" >
    </cfstoredproc>

    <cflock scope="application" type="exclusive" timeout="20">
        <cfset application.qry.currency = duplicate(qry) />
    </cflock>
</cffunction>   

Answer 1

Based on the details so far, to accomplish what you're asking.....

I'm assuming your stored procedure looks something like this:

CREATE PROCEDURE dbo.usp_get_rates @origCode varchar(20), @toCode varchar(20)
AS  
BEGIN 
  SET NOCOUNT ON ;
  SELECT code, rate
  FROM dbo.currency
  WHERE code IN ( @origCode, @toCode )
END;  
GO  

NOTE: You'd want to replace "dbo" with whatever your appropriate db schema is. But if you are querying across databases (see comment above about application.qry.currency), then you'd keep your FROM call the same and place/reference your stored procedure in the appropriate schema.

With that procedure, your ColdFusion code would be like:

<cfstoredproc procedure="usp_get_rates" datasource="#variables.dsn#">
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@origCode" type="in" value="#arguments.origCode#">
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@toCode" type="in" value="#arguments.toCode#">
        <cfprocresult name="rates">
</cfstoredproc>

<cfscript>
  // Initialize your variables. They won't exist if your query has 0 rows.
  var origRate = 0 ; // Or expected datatype
  var toRate   = 0 ; // Or expected datatype

  for (var thisrow in rates) {
     if ( thisrow.code == arguments.origCode ) { origRate = thisrow.rate ; }
     if ( thisrow.code == arguments.toCode) { toRate = thisrow.rate ; }
  }
</cfscript>

Since you are using arguments scope, I'm assuming that this code is inside a function. That would allow you to use the var keyword.

Note that dbvarname was ignored since CFMX and reinstated in CF11 Update 3. CF11 had a JVM flag ( -Dcoldfusion.ignoredbvarname ) to continue to ignore dbvarname, but that flag is ignored in CF2016+.

You can use a simple for loop to iterate over the query, but as I said above, this may not be what you really want. If your query/sproc returns multiple results, it will overwrite origRate or toRate if there are more than one value returned for them. ie if your query has [{code:"x",rate:41},{code:"x",rate:42}], it will overwrite rate for code="x".