Two ways to do it.
1) In stored procedure you can build a sql string and then execute using the
exec commmand.
sql="Select * From Listings2 Where (ID >0) "
if @name <>""
begin
sql=sql + " AND ((Name LIKE '%#name#%'))
end
if..........
sql=sql + ORDER BY #OrderBy#
exec sql
Thats easier to build and more flexible, but less efficent since the query
plans can't be optimised.
2)Conditional statements to select a full query
if whatever
begin
select whatever --full select statement
end
.....
This would be messy to code for your example but would allow for
optimisation.
-----Original Message-----
From: metzy martinez [mailto:[EMAIL PROTECTED]]
Sent: 07 March 2001 19:01
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
