Need help with building a custom query

[Stanley_G]

Hi everybody.

How can I create a custom query based on argument passed to CFC?

Here is a sample code:

<cffunction name="get_all_brands" access="remote" returntype="query">
<cfargument name="show_hidden" type="boolean" required="no" default="false"
/>

<cfset var all_brands_qry = "" />

<cfif show_hidden is true>
<cfquery name="all_brands_qry" datasource="cupoint">
SELECT dbo.cupoint_item_brand.intBrandID as BrandID,
dbo.cupoint_item_brand.vchBrandName as BrandName,
dbo.cupoint_item_brand.bitActive as BrandActive
FROM dbo.cupoint_item_brand
</cfquery>
<cfelse>
<cfquery name="all_brands_qry" datasource="cupoint">
SELECT dbo.cupoint_item_brand.intBrandID as BrandID,
dbo.cupoint_item_brand.vchBrandName as BrandName,
dbo.cupoint_item_brand.bitActive as BrandActive
FROM dbo.cupoint_item_brand
WHERE dbo.cupoint_item_brand.bitActive = 1
</cfquery>
</cfif>

<cfreturn all_brands_qry />
</cffunction>

Is there a way to filter argument "show_hidden" through cfif and just add
"WHERE dbo.cupoint_item_brand.bitActive = 1" to the first portion of SQL query?
I should be easy. I come from ASP bg and just need a quick tip.

Thank you.

SG

[CFDEBUG]

You could do

<cffunction name="get_all_brands" access="remote" returntype="query">
<cfargument name="show_hidden" type="boolean" required="no" default="false" />
<cfquery name="all_brands_qry" datasource="datasourcename">
SELECT dbo.cupoint_item_brand.intBrandID as BrandID,
dbo.cupoint_item_brand.vchBrandName as BrandName,
dbo.cupoint_item_brand.bitActive as BrandActive
FROM dbo.cupoint_item_brand
<cfif not show_hidden>
WHERE dbo.cupoint_item_brand.bitActive = 1
</cfif>
</cfquery>
<cfreturn all_brands_qry />
</cffunction>