Re: Fulltext Search in SQL 2008/2008 R2

Question

In Sql server 2005 below code work and display all records when pass null parameter

I installed latest service pack and CU.

declare @find nvarchar(50)=''
select * from TestFull where contains(fname,@find)

--------

But In sql server 2008 / R2 not work

declare @find nvarchar(50)=''

--or

declare @find nvarchar(50)='""'

--or

declare @find nvarchar(50)

select * from TestFull where contains(fname,@find)

This code not dispaly all records , How to pass null variable in SQL 2008/R2 for Fulltext search .

Answer 1

Hi vipul,

I would like to recommend you to refer to this thread:

http://stackoverflow.com/questions/5875928/sql-server-free-text-search-with-empty-keyword

Sample statements are posted by Seph.

Previous code:

DECLARE @pSearchFor AS NVARCHAR(100); 
SET @pSearchFor = 'SomeKeyword'; 
 
SELECT MS.[ModuleScreenID] AS ScreenID 
    ,MS.[ModuleScreenCode] AS ScreenCode 
    ,M.[Description] AS ModuleDescription 
    ,M.[ModuleCode] AS ModuleCode         
    ,FT.[Rank]  
FROM ModuleScreen MS 
    JOIN Module M ON MS.ModuleID = M.ModuleID 
    JOIN CONTAINSTABLE(ModuleScreen, *, @pSearchFor) FT ON MS.ModuleScreenID = FT.[KEY] 

Updated code
DECLARE @pSearchFor AS NVARCHAR(100); 
SET @pSearchFor = 'SomeKeyword'; 
 
IF @pSearchFor IS NOT NULL AND @pSearchFor <> ''  
BEGIN 
    SELECT MS.[ModuleScreenID] AS ScreenID 
        ,MS.[ModuleScreenCode] AS ScreenCode 
        ,M.[Description] AS ModuleDescription 
        ,M.[ModuleCode] AS ModuleCode         
        ,FT.[Rank]  
    FROM ModuleScreen MS 
        JOIN Module M ON MS.ModuleID = M.ModuleID 
        JOIN CONTAINSTABLE(ModuleScreen, *, @pSearchFor) FT ON MS.ModuleScreenID = FT.[KEY] 
END 
ELSE 
BEGIN 
    SELECT MS.[ModuleScreenID] AS ScreenID 
        ,MS.[ModuleScreenCode] AS ScreenCode 
        ,M.[Description] AS ModuleDescription 
        ,M.[ModuleCode] AS ModuleCode         
        ,FT.[Rank]  
    FROM ModuleScreen MS 
        JOIN Module M ON MS.ModuleID = M.ModuleID 
END 

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi

You right , but so many place right code . In my code so many places changes ,

Any other method ?

Regards

Answer 3

Well, how about trying this, which is a bit simpler:

declare @find nvarchar(50)
-- Assumes NULL, blank, or 0 length string for no entry.  
SET @find = COALESCE(NULLIF(RTRIM(LTRIM(@find)),''),'""')
select * from TestFull 
where contains(fname,@find)
  OR @find = '""'

FWIW,
RLF