my database is sql server 7.0 The problems is that i'm trying to make a
dynamic WHERE statement and i'm trying to find out if you can use them
Where (ID >0)<cfif name is not "">AND((Name LIKE '%#name#%'))</cfif><cfif
list is not "">AND(list LIKE '%#list#%'))</cfif>
in this query is trying to figure out if the user entered something into the
name field and if they did they are appending the statement to the where
clause
> -----Original Message-----
> From: Alex [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 07, 2001 12:23 PM
> To: CF-Talk
> Subject: Re: [HELP going from Dynamic CF Queries to Stored Procedures]
>
>
> you can't use ColdFusion ifs but you can use your DB's ifs.
> what's your DB?
>
> "metzy martinez" <[EMAIL PROTECTED]> wrote:
>
>
> 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
>
> <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
