2

I'm trying to do some query performance and I've been looking into applying a FULLTEXT INDEX as compared to using LIKE.

I have a database w/person information, i.e. FirstName, LastName, etc. where some of those columns are null.

When I try to use CONTAINS object, I'm getting this error message; 

Null or empty full-text predicate

I've put in code to ensure the passed in @FirstName field is not null, and if a null was passed in then I've set it to ' "" ' like I've read elsewhere. I still get the error message. I'm starting to wonder if it's a problem because the index has columns that are null.

Does anyone know if that is the case, I've looked everywhere and I cannot find any information on this.

Here is the query. The @FirstNameLocal and @LastNameLocal are passed into this stored procedure. In this example, FirstName and/or LastName field data in the PersonTable could be null. I'm using SQL Server 2012 w/SP1. "Try to use" means when I try to run the query in SQL Management Studio.

SELECT DISTINCT LastName, FirstName, MiddleName, PersonId
FROM PersonTable
Where IsDeleted = 0
AND CONTAINS(FirstName, @FirstNameLocal)
AND CONTAINS(LastName, @LastNameLocal)
Rob Stewart
  • 441
  • 4
  • 14
  • did you try this? http://stackoverflow.com/questions/189765/7645-null-or-empty-full-text-predicate – techspider Jan 18 '16 at 22:20
  • Show us your current query –  Jan 18 '16 at 22:30
  • Also please: What does "try to use" mean? Compile? Run? Give us your SQL Server version and table definitions. And if applicable example input & output and desired output. Read about MVCEs. – philipxy Jan 18 '16 at 23:00
  • Did my answer help you in any way? I'm not seeing upvotes or acceptance of my answer... Just wondering. – TT. Jan 21 '16 at 21:51

1 Answers1

0

You could check whether your variable IS NULL or equals an empty string first. I think though that this will partially negate the benefit of full-text indexes and you should consider handling this differently (for instance with dynamic SQL).

SELECT DISTINCT LastName, FirstName, MiddleName, PersonId
FROM PersonTable
Where IsDeleted = 0
AND (ISNULL(@FirstNameLocal,'')='' OR CONTAINS(FirstName, @FirstNameLocal))
AND (ISNULL(@LastNameLocal,'')='' OR CONTAINS(LastName, @LastNameLocal));

If you would do this with dynamic SQL it would look something like this:

DECLARE @stmt NVARCHAR(4000);

SET @stmt=
    N'SELECT DISTINCT LastName, FirstName, MiddleName, PersonId ' +
    N'FROM PersonTable ' +
    N'Where IsDeleted = 0' +
    CASE WHEN ISNULL(@FirstNameLocal,'')='' 
        THEN N''
        ELSE N' AND CONTAINS(FirstName, @FirstNameLocal)'
    END +
    CASE WHEN ISNULL(@LastNameLocal,'')='' 
        THEN N''
        ELSE N' AND CONTAINS(LastName, @LastNameLocal)'
    END + ';'

EXEC sp_executesql 
    @stmt,
    N'@FirstNameLocal VARCHAR(256),@LastNameLocal VARCHAR(256)',
    @FirstNameLocal,
    @LastNameLocal;

The downside is that the statement is compiled again each time this script is run, but this will be small compared to the benefit of full-text indexes that can be used in this statement.

Always compare the Actual Execution Plans to choose which version is best.

TT.
  • 15,774
  • 6
  • 47
  • 88