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

What do you expect to get when you search for an empty or null value? do you expect the query to return nothing or do you expect it to return something else.

If you want it to return nothing then you're best of doing something like this:

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

edit: fixed to now return all records when null or empty string is supplied.

If you have more than 2 CONTAINS table queries with different search strings, then I would suggest you generate the query using dynamic SQL as it would be much easier to maintain than a 2^n chain of almost the same queries

Edit: having looked at a way to do this without using multiple copies by using a temp table like this:

DECLARE @pSearchFor AS NVARCHAR(100);
SET @pSearchFor = 'SomeKeyword';

SELECT * INTO #temp FROM CONTAINSTABLE(ModuleScreen, *, @pSearchFor)

SELECT MS.[ModuleScreenID] AS ScreenID
    ,MS.[ModuleScreenCode] AS ScreenCode
    ,M.[Description] AS ModuleDescription
    ,M.[ModuleCode] AS ModuleCode        
    ,FT.[Rank] 
FROM Module M
    JOIN ModuleScreen MS ON MS.ModuleID = M.ModuleID AND (
        (1 = CASE WHEN ISNULL(@pSearchFor, '') = '' THEN 1 ELSE 0 END
        OR CONTAINS(MS.*, @pSearchFor)
    LEFT OUTER JOIN #temp FT ON MS.ModuleScreenID = FT.[Key]

This should give you what you want without having to duplicate things, however you might want to restrict the result that is fed into the #temp table some more as it will get slow for larger tables.

Answer 2

I had also the exact same issue and solved it by adding/appending a dummy keyword 'fts' to the search indexed column for all the records.

if(nullif(@pSearchFor,'') is null)
begin
     set @pSearchFor= 'fts';                
end

Answer 3

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.