SQL Server - Free text search with empty keyword

Question

Below is a simplified version of my sql query that uses CONTAINSTABLE for full text searching.

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]

I want to pass empty or null value for @pSearchFor parameter so that all records are returned by full text search. But I get a "Null or empty full-text predicate" error when I pass empty or null value. After googling, I found that CONTAINSTABLE cannot take an empty parameter for keywords. I have also seen this question in SO but it did not help me.

Can I make a conditional join with CONTAINSTABLE (only when a value is specified for @pSearchFor parameter)?. I am not sure how to achieve this. Would appreciate any pointers.

Answer 1

DECLARE @pSearchFor AS NVARCHAR(100);

SET @pSearchFor = 'SomeKeyword';
--if @pSearch comes as parameter then --
set @pSearch = ISNULL(@pSearch,'*')

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]
where @pSearchFor = '*' OR FT.[KEY] is not null

I just solved the exact same issue and thought of helping you out.