Re: Using CASE statement in CONTAINS predicate ( For FullText Sear

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:96BA7529-88B1-4587-8FC0-5BEB31A79BE8@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: mail merge filter criteria
    ... and that it will occur as soon as you have an OLE DB connection to a data source and specify "is blank" or perhaps "is not blank" as one of the criteria in your "Advanced criteria". ... is null (which in Excel appears to correspond to a cell with no text or formula in it ... The Jet SQL that is used to get data from Excel actually retrieves both ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQL queries that have parameters passed by user
    ... Erland Sommarskog wrote: ... >> several fields and filters on a criteria that I would like to be user ... >> The query works fine in MS SQL and seems to be fairly quick, ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: QueryTables - Retrieving Data
    ... data in Excel then filter it using cell formulas, pass your criteria ... ('sql datasource' is particularly vague; if you mean SQL Server then ... The column has an eight digit account code that I need to ... When I 'double click' a cell in the accountnumber ...
    (microsoft.public.excel.misc)
  • Re: multiple OR criteria
    ... Based on the SQL, your table is more like a spreadsheet than a relational ... those criteria? ... I have set up multiple OR criteria in the query looking for ...
    (microsoft.public.access.queries)