Dynamic use of CONTAINS(column, 'words')

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic use of CONTAINS(column, 'words')

Author

Topic

steffen_soegaard
Starting Member

2 Posts

Posted - 2001-12-04 : 08:11:12

I need to use the CONTAINS in my SP if the @AllowedKeyWords parameter is not NULL...

SELECT
...
...
WHERE
This and That [COMMON WHERE CLAUSE]
AND
CONTAINS(KeyWords, @AllowedKeyWords)

This is cool as long as the @AllowedKeyWords holds a valid search criteria.
But I need it top be ignored if @AllowedKeyWords is NULL, thus returning all records that match my common WHERE CLAUSE.

best regards
Steffen S. Mortensen

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-12-04 : 12:08:36

have a search here for DYNAMIC SQL.....may even be a FAQ or an ARTICLE on it....it should point you in the right direction.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 12:27:47

Does this work?


 ...
 AND CASE
     WHEN @AllowedKeyWords IS NULL THEN 1
     WHEN CONTAINS(KeyWords, @AllowedKeyWords) THEN 1
     ELSE 0
   END = 1

Edited by - Arnold Fribble on 12/04/2001 12:28:04

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-04 : 12:35:38

<tease> It's not sargable </tease>

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11100

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 12:41:41

I know it's a tease, but having never used full-text indexing, I have no idea how the query optimizer organizes full-text and SQL conditions. Hmm, maybe I will have a play around with it...

steffen_soegaard
Starting Member

2 Posts

Posted - 2001-12-05 : 03:35:12

Yes this works! Very nice! Thank You...

quote:

Does this work?


 ...
 AND CASE
     WHEN @AllowedKeyWords IS NULL THEN 1
     WHEN CONTAINS(KeyWords, @AllowedKeyWords) THEN 1
     ELSE 0
   END = 1

Edited by - Arnold Fribble on 12/04/2001 12:28:04

best regards
Steffen S. Mortensen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources