> 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
> "Hilary Cotter" wrote:
>
>> 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)
>>
>> -- 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
>> "Vijay" <Vijay@discussions.microsoft.com> wrote in message
>> news:96BA7529-88B1-4587-8FC0-5BEB31A79BE8@microsoft.com...
>>> 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
>>>

"Simon Sabin" wrote:

> 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,
> >
> > 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
> > "Hilary Cotter" wrote:
> >
> >> 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)
> >>
> >> -- 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
> >> "Vijay" <Vijay@discussions.microsoft.com> wrote in message
> >> news:96BA7529-88B1-4587-8FC0-5BEB31A79BE8@microsoft.com...
> >>> 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
> >>>
>
>