Passing SQL Variable to CF Function

[romeogq]

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.

[Newsgroup_User]

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