-----Original Message-----
From: metzy martinez [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 07, 2001 1:01 PM
To: CF-Talk
Subject: HELP going from Dynamic CF Queries to Stored Procedures
Our company is going from using regular cfquery statements to putting them
into stored procedures in order to get the load of our cold fusion box and
letting the sql server box handle the load. where i'm having problems is
making a query that is dynamic and changing it to a stored procedure. i'm
wondering things like can i use if statements in my wheres. Attached is an
example of what is driving me insane.
<cfquery name="getList" datasource="#dsn.LocalGuide#" dbtype="ODBC">
Select *
>From Listings2
Where (ID >0)
<cfif name is not "">AND
((Name LIKE '%#name#%'))</cfif>
<cfif list is not "">AND
((list LIKE '%#list#%'))</cfif>
<cfif category is not "">AND
((CategoryMain = '#category#') OR
(CategoryOther = '#category#'))</cfif>
<cfif subs1 is not ""> AND
((Subs1 = '#subs1#') OR
(SubsOther = '#subs1#') )</cfif>
<cfif street is not ""> AND
(Street1 LIKE '%#street#%')</cfif>
<cfif moviecode is not ""> AND
(moviecode = '#moviecode#')</cfif>
<cfif keyword is not "">AND
((Keywords LIKE '%#keyword#%') OR
(Name LIKE '%#keyword#%') OR
(Description LIKE '%#keyword#%') OR
(CategoryMain LIKE '%#keyword#%') OR
(CategoryOther LIKE '%#keyword#%') OR
(Subs1 LIKE '%#keyword#%') OR
(SubsOther LIKE '%#keyword#%'))</cfif>
<cfif area is not "">AND (AreaOfTown = '#area#') </cfif>
<cfif yourReview is "on">AND (NumberOfVotes > 0)</cfif>
<cfif criticReview is "on">AND (reviewid <> '') </cfif>
<cfif award is "on">AND (AwardsID <> '')</cfif>
ORDER BY #OrderBy#
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
