Sql Server conditional Contains for free text search handling of Null parameter

I have been struggling for quite some time to get this query going.

In short my query searches by fileno and/or searchfield

DECLARE @pSearchFor AS NVARCHAR(100);

-- I am here testing with null value, ' ' , or seperate words SET @pSearchFor = null -- '"marsa" and "mosta"';

 IF ISNULL(@pSearchFor,'') = '' SET @pSearchFor = '""' ;

declare @fileNo nvarchar(50) = 'e/e'

select top 1000 r.FileId, r.FileNo,  fs.SearchField, @pSearchFor

from regfile as r
left outer join FileSearchFields as fs on r.FileId = fs.FileID
where r.FileNo like 
CASE 
 WHEN  Len(@fileno) > 1 THEN '%'+@fileNo+'%'
 ELSE r.FileNo
END

 AND 
     1 = 
     CASE WHEN ISNULL(@pSearchFor, '') = '' THEN 1 ELSE 0 END
       or CONTAINS(fs.SearchField, @pSearchFor)

I am getting nothing returned if @开发者_StackOverflow社区pSearchFor is null otherwise works great.

I need to return all instances if a null

One possible solution might be to call 2 seperate sps or use if /else but probably exists a better method.

I really do appreciate your help!

First you set @pSearchFor to "":

IF ISNULL(@pSearchFor,'') = '' SET @pSearchFor = '""' ;

That means this will never return 1:

CASE WHEN ISNULL(@pSearchFor, '') = '' THEN 1 ELSE 0 END

You need to either use a different variable, or use the same type of CASE expression in the select list, instead of changing the value from NULL to "".

SELECT TOP 1000 r.FileId, r.FileNo,  fs.SearchField, 
    CASE WHEN COALESCE(@pSearchFor, '') = '' THEN '""' ELSE @pSearchFor END

Also you use SELECT TOP but no ORDER BY ... if you want a subset, don't you care which subset you get?

I have solved the problem. Maybe this may be of some help to others! This is a snippet of my stored procedure.

 @fileNo nvarchar(50) = null ,
 @fields nvarchar(100) = '""',`enter code here`
 @datefrom date = null,
 @dateto date  = null,
...
AS`enter code here`
BEGIN

 if (@fields = null or LEN(@fields) < 1 ) set @fields = '""'

select top 1000 r.*,
(CASE 
        WHEN fs.SearchField IS NULL THEN CONVERT(NVarChar(1),'')
        ELSE CONVERT(NVarChar(MAX),fs.SearchField)
     END) AS [Search]  
from regfile as r
left outer join FileSearchFields as fs on r.FileId = fs.FileID
where r.FileNo like 
CASE 
 WHEN  Len(@fileno) > 1 THEN '%'+@fileNo+'%'
 ELSE r.FileNo
 END
 and
  r.Date between 
CASE 
 WHEN  @datefrom != '' THEN @datefrom
 ELSE '1900-1-1'
 END

 and 
CASE 
 WHEN  @dateto != '' THEN @dateto
 ELSE '9999-1-1'
 END
 and 
 ((LEN(@fields) > 2 and contains(fs.SearchField,@fields))or (LEN(@fields) <= 2)) 
 --NB: <= 2 as we have added the "" characters in @fields!

end