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:202436
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
