We don't know. I was trying to see if it was even possible before attempting.
Thanks Scott Scott A. Stewart Webmaster/ Developer GlobalNet Services, Inc http://www.gnsi.com 11820 Parklawn Dr Rockville, MD 20852 Voice: (301) 770-9610 x 335 Fax: (301) 770-9611 The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or any employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Saturday, April 01, 2006 6:47 AM To: CF-Community Subject: Re: SQL Question, Switch Case > Here's what I have, it was written by another developer but I've been tasked > with trying to make it run faster > > <cfquery name="qEntityInformation" datasource="#AppDataSource#"> > SELECT top 100 > INVN AS EntityId, ... > FROM ENTITY_ALL > WHERE 1 = 1 > <cfif attributes.Keywords neq ""> > AND > <cfswitch expression="#attributes.DataField#"> > <cfcase value="EntityId"> > ENTITY_ALL.INVN LIKE '%#attributes.Keywords#%' > </cfcase> ...... > <cfdefaultcase> > ((ENTITY_ALL.INVN LIKE '%#attributes.Keywords#%') OR > (FNAME LIKE '%#attributes.Keywords#%') OR > (SNAME LIKE '%#attributes.Keywords#%') OR > (SITE LIKE '%#attributes.Keywords#%') OR > (SADDR LIKE '%#attributes.Keywords#%') OR > (CITY LIKE '%#attributes.Keywords#%') OR > (STATE LIKE '%#attributes.Keywords#%') OR > (COUNTRY LIKE '%#attributes.Keywords#%') OR > (ZIP LIKE '%#attributes.Keywords#%') OR > (TYPE LIKE '%#attributes.Keywords#%')) > </cfdefaultcase> > </cfswitch> > </cfif> > ORDER BY #attributes.SortField# #SortDirection# > </cfquery> > What I want to do is pass attributes.DataField to a stored procedure and > build the where clause based on it. Why do you think converting this to a stored procedure is going to make it significantly faster? What does the explain output tell you about the relative timing of planning and execution? Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202525 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
