You can do something like the code below. The default case (for all) cannot
be handled by a case statement, sot it has to be handled by a separate query
(chosen by an if statement). Also, I am not on MS-SQL server any more
(currently using oracle) and I am not sure if I remember the concatenation
operator correctly for the like statement in the second query. I think its
the plus sign, but could be wrong. Either way, the "all" query is going to
be pretty slow with all of those likes where its in a stored procedure or
not. I doubt you will see much performance improvement from this since (in
my understanding) like comparisons do not use indexes.
You might be better off adding a full text index to the columns and use the
contains function to do the searches. This will take more space and
decrease write performance but will speed up the retrieval with or without
the stored procedure.
Example:
IF @@DATA_FIELD = 'ALL'
BEGIN
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 CASE
WHEN @DATA_FIELD = 'EntityId' THEN ENTITY_ALL.INVN
WHEN @DATA_FIELD = 'FirstName' THEN FNAME
WHEN @DATA_FIELD = 'LastName' THEN SNAME
WHEN @DATA_FIELD = 'SiteName' THEN SITE
WHEN @DATA_FIELD = 'SiteAddress' THEN SADDR
WHEN @DATA_FIELD = 'City' THEN CITY
WHEN @DATA_FIELD = 'StateCode' STATE
WHEN @DATA_FIELD = 'CountryCode' COUNTRY
WHEN @DATA_FIELD = 'PostalCode' ZIP
WHEN @DATA_FIELD = 'EntityType' TYPE
END LIKE @KEYWORD
END
ELSE
BEGIN
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 ENTITY_ALL.INVN LIKE '%' + @KEYWORD + '%'
OR FNAME LIKE '%' + @KEYWORD + '%'
OR SNAME LIKE '%' + @KEYWORD + '%'
OR SITE LIKE '%' + @KEYWORD + '%'
OR SADDR LIKE '%' + @KEYWORD + '%'
OR CITY LIKE '%' + @KEYWORD + '%'
OR STATE LIKE '%' + @KEYWORD + '%'
OR COUNTRY LIKE '%' + @KEYWORD + '%'
OR ZIP LIKE '%' + @KEYWORD + '%'
OR TYPE LIKE '%' + @KEYWORD + '%'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202445
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=89.70.5
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
