You don't have to include all those if statements. Blah like "%%" will do what you need. If the variable is empty it will evaluate for true on all of those.
> -----Original Message----- > From: Scott Stewart [mailto:[EMAIL PROTECTED] > Sent: Friday, March 31, 2006 1:54 PM > To: CF-Community > Subject: RE: SQL Question, Switch Case > > Chris, > > 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, > FNAME AS FirstName, > SNAME AS LastName, > MINIT AS MiddleInitial, > RANK AS NameSuffix, > DEG AS Degree, > SITE AS SiteName, > SADDR AS SiteAddress, > CITY AS City, > STATE AS StateCode, > COUNTRY AS CountryCode, > ZIP AS PostalCode, > TYPE AS EntityType, > Processed, > RCVDLIST AS SubmissionDateList, > 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> > <cfcase value="FirstName"> > FNAME LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="LastName"> > SNAME LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="SiteName"> > SITE LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="SiteAddress"> > SADDR LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="City"> > CITY LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="StateCode"> > STATE LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="CountryCode"> > COUNTRY LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="PostalCode"> > ZIP LIKE '%#attributes.Keywords#%' > </cfcase> > <cfcase value="EntityType"> > TYPE 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. > > 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: Chris Stoner [mailto:[EMAIL PROTECTED] > Sent: Friday, March 31, 2006 1:39 PM > To: CF-Community > Subject: Re: SQL Question, Switch Case > > Well it *would* work but its will probably not give you what you are > looking > for in the format you have it (assumption on my part). Basically what the > where clause is saying is where @someVar = (the result of the case > statement) > > So assuming in your example that @someVar is coming in with a value of > 'A', > this where clause will equate to WHERE 'A' = @ARGUMENT_A. While this > should work syntactically, I just don't see how that can be your goal. > Its > hard to tell without a clear example of what you are trying to accomplish. > If you want to post an example I can probably help you more easily. > > > On 3/31/06, Scott Stewart <[EMAIL PROTECTED]> wrote: > > > > Will this work > > > > DECLARE @some var > > > > SELECT * > > FROM MY_FAKE_TABLE > > WHERE @some var = CASE > > WHEN @some var = 'A' THEN @ARGUMENT_A > > WHEN @some var = 'B' THEN @ARGUMENT_B > > ELSE @ARGUMENT_DEFAULT > > END > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202439 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
