Using CASE statement in CONTAINS predicate ( For FullText Search)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vijay - 22 Oct 2007 12:39 GMT

Hi,

I am using Microsoft FullText search feature in my database for Searching
strings.

I want to use CASE statement in CONTAINS.
DECLARE @allowSearchALL int

SELECT [ID] ,[Source] ,[Description],name

FROM [Search]

WHERE CONTAINS( Source , '"mail"')

i know we can use CASE in WHERE clause...but dono how to use CONTAINS with
CASE.

My Requirement is

if @allowSearchALL is 1 then get all records otherwise use specified
hardcoded text "mail"

I have to use CONTAINS.

I would like use some thing like this,

SELECT [ID] ,[Source] ,[Description],name

FROM [Search]

WHERE Source = CASE WHEN @allowSearchALL = 1 THEN Source ELSE 'mail' END

This way i can map column name to same column name if @allowSearchALL = 1 .
I do not want to use multiple IF blocks like if ( )
begin

end
else if( )
begin

end

How this can be done in CONTAINS ? like WHERE CONTAINS(source, CASE ....ELSE
...END)

Please give me solution for this.

Thanks in advance

Reply to this Message

Hilary Cotter - 23 Oct 2007 01:53 GMT

You need an if statement

if @allowSearchAll=1

SELECT [ID] ,[Source] ,[Description],name

FROM [Search]

else SELECT [ID] ,[Source] ,[Description],name

FROM [Search] WHERE contains(*,'mail)

Signature

RelevantNoise.com - dedicated to mining blogs for business intelligence.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi,
>
[quoted text clipped - 46 lines]
>
> Thanks in advance

Reply to this Message

Vijay - 23 Oct 2007 08:34 GMT

Hi,

I donot want to use Multiple IF blocks as my IF criterias are huge...i want
to use some thing like

SELECT [ID] ,[Source] ,[Description],name
FROM [Search]
WHERE Source = CASE WHEN @allowSearchALL = 1 THEN Source ELSE 'mail' END

> You need an if statement
>
[quoted text clipped - 58 lines]
> >
> > Thanks in advance

Reply to this Message

Daniel Crichton - 23 Oct 2007 09:11 GMT

Vijay wrote on Tue, 23 Oct 2007 00:34:09 -0700:

> Hi,

> I donot want to use Multiple IF blocks as my IF criterias are huge...i
> want to use some thing like

> SELECT [ID] ,[Source] ,[Description],name
> FROM [Search]
> WHERE Source = CASE WHEN @allowSearchALL = 1 THEN Source ELSE 'mail'
> END

You can't - Hilary was suggesting the only viable alternative, other than
creating dynamic SQL.

Signature

Dan

Reply to this Message

Simon Sabin - 24 Oct 2007 20:38 GMT

Hello Vijay,

Daniel is correct. You don't have that option.

You best solution from a performance perspective is to use dyanmic SQL and
only add the criteria you need and then execute that. Make sure you use sp_executesql
and parameterise your query.

i.e.
declare @sql nvarchar(max)
declare @where nvarcha(max)
set @sql = 'SELECT [ID] ,[Source] ,[Description],name FROM [Search]'

if @allowSearch <> 1
set @where = 'contains (*,''mail'')'

set @sql = @sql + ' WHERE ' + @where

exec sp_executesql @sql

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
>
[quoted text clipped - 73 lines]
>>>
>>> Thanks in advance

Reply to this Message

Vijay - 29 Oct 2007 15:19 GMT

Hi Simon Sabin,

Thanks for you reply.

How about this implementation...
SELECT [ID] ,[Source] ,[Description], name
FROM [Search]
WHERE (@allowSearchALL = 1) OR CONTAINS( Source , 'mail')

Pls suggest if this option is fine? will there be any performance hit ..if i
use the above query??

Thanks
Vijay

> Hello Vijay,
>
[quoted text clipped - 97 lines]
> >>>
> >>> Thanks in advance

Reply to this Message