1 Reply Latest reply on Dec 29, 2008 4:45 AM by Newsgroup_User

    Passing SQL Variable to CF Function

    romeogq Level 1
      MS SQL Database

      The statement first uses the function to convert the currency of 'arguments.info.amountMax' from 'session.member.currency_id' to 'p.currency_id'. Then it is to be compared to the 'p.property_saleprice'.
      The 'p.currency_id' is currently not passing the dynamic SQL variable. Any idea on how I can get it to do so?

      Thanks in advance.
        • 1. Re: Passing SQL Variable to CF Function
          Level 7
          romeogq wrote:
          > MS SQL Database
          >
          > The statement first uses the function to convert the currency of
          > 'arguments.info.amountMax' from 'session.member.currency_id' to
          > 'p.currency_id'. Then it is to be compared to the 'p.property_saleprice'.
          > The 'p.currency_id' is currently not passing the dynamic SQL variable. Any
          > idea on how I can get it to do so?
          >
          > Thanks in advance.
          >
          > SELECT p.*
          > FROM ------
          > WHERE ------
          > AND p.property_saleprice <= <cfqueryparam
          > value="#convertedCurrency(arguments.info.amountMax, session.member.currency_id,
          > '@p.currency_id')#" cfsqltype="cf_sql_money" />

          You cannot mix CF and SQL in the way you want it (calling a CF function
          from SQL). You'll need to either:
          - call the function before the query, generating a constant that you'll
          send then to the SQL Server;
          - process the query yourself after you get the results from the database
          (call the CF function on all rows of the result):
          <cfloop query="result">
          <cfset t = convertedCurrency(arguments.info.amountMax,
          session.member.currency_id, .currency_id)>
          ....
          </cfloop>

          --
          Mack