How about creating a view in SQL Server that shows all the data without the
Where clause, then you can just :
select your, fields from theview where whatever
It'll save you from doing all the joins over and over again in CF and it'll
be optimised by SQL Server, all you have to worry about is your WHERE
clause.
Incidently, in your CFIFs :
(#Attributes.MSA# NEQ "'ZZZ'")
should be :
(Attributes.MSA NEQ "'ZZZ'")
etc.
Its a bit more efficient than CF having to convert the contents of the
variable into a real value before it does the comparison....
Also, its better practice to build your where clause outside of the CFQUERY.
Regards
Stephen
> -----Original Message-----
> From: BT [mailto:[EMAIL PROTECTED]]
> Sent: 02 August 2001 07:47
> To: CF-Talk
> Subject: Dynamic Stored Procedure
>
>
>
>
>
>
> I guys we have a cfquery that represents the below but it has inner joins
> with CF conditional logic in it...
>
>
>
> I was wondering if there is a way in SQL on SQL7 to say if this
> variable is
> passed the add a where clause or something......
>
>
>
> <CFQUERY NAME="JobSearch2" DATASOURCE="webdb" MAXROWS="1000"
> BLOCKFACTOR="#Attributes.numrows#">
> SELECT Jobs.JobID, Jobs.DateLastUpdated, Jobs.Title, Jobs.EID,
> Jobs.DisplayEmployerName,
> Jobs.Country, Jobs.EmpJobID, Jobs.Description, Jobs.City,
> Jobs.Image AS Image,
> JobListMSA.MSA AS MSA, JobListMSA.JobID AS JobID2,
> JobsMSALookup.MSA AS MSA2
> FROM JobsMSALookup INNER JOIN
> JobListMSA ON
> JobsMSALookup.ID = JobListMSA.MSA INNER JOIN
> Jobs ON JobListMSA.JobID = Jobs.JobID
> WHERE #sitequal#
> <CFIF ValidDate IS NOT "NO">
> AND (DateLastUpdated >= #Dates#)
> </CFIF>
> <CFIF (#Attributes.class# NEQ "'any'") AND
> (#Len(Attributes.class)#
> gt 1)>
> AND ((Classification IN
> (#PreserveSingleQuotes(Attributes.class)#))
> OR (Class2 IN (#PreserveSingleQuotes(Attributes.class)#))
> OR (Class3 IN (#PreserveSingleQuotes(Attributes.class)#))
> OR (Class4 IN (#PreserveSingleQuotes(Attributes.class)#)))
> </CFIF>
> <CFIF (#Attributes.MSA# NEQ "'ZZZ'") AND (#Attributes.MSA# NEQ
> "'NAT'") AND (#Len(Attributes.MSA)# GT 3)>
> AND (JobListMSA.MSA IN
> (#PreserveSingleQuotes(Attributes.MSA)#))
> </CFIF>
> <CFIF (#Attributes.Country# NEQ "'any'") AND ("2,7,10" DOES NOT
> CONTAIN #Attributes.searchtype#)>
> AND (Country IN (#PreserveSingleQuotes(Attributes.Country)#))
> <CFELSEIF (#Attributes.Country# NEQ "'any'") AND
> ("2,7,10" CONTAINS
> #Attributes.searchtype#)>
> AND (Country IN (#PreserveSingleQuotes(Attributes.Country)#))
> <CFELSEIF (#Attributes.Country# EQ "'any'") AND ("9" CONTAINS
> #Attributes.searchtype#)>
> <!---AND (Country IN
> (#PreserveSingleQuotes(Attributes.Country)#))--->
> <CFELSE>
> AND (Country <> 'United States')
> </CFIF>
> <CFIF #Querystring# NEQ "">
> AND (#PreserveSingleQuotes(Querystring)#)
> </CFIF>
> ORDER BY eid desc, datelastupdated desc
> </CFQUERY>
>
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
