Handling nulls on freetext

Question

I am trying to use FREETEXT. The @name field often has nulls and this is not an error in the program. It just means that other search approaches are being used.

Originally I had

DECLARE @Name varchar(50) = ... -- some value which many be NULL or blank

SELECT ID 
FROM dbo.Data WITH (NOLOCK) 
WHERE  FREETEXT(*, @Name)

I tried

DECLARE @Name varchar(50) = ... -- some value which many be NULL or blank

SELECT ID 
FROM dbo.Data WITH (NOLOCK) 
WHERE @Name IS NOT NULL AND  FREETEXT(*, @Name)

But I still get

Msg 7645, Level 15, State 2, Line 3 Null or empty full-text predicate.

What is a good approach to get around this?

additional note

SELECT ID 
FROM dbo.data WITH (NOLOCK) 
WHERE FREETEXT(*, ' ')

also throws and error

Answer 1

Update

try this:

SET @Name = ISNULL(NULLIF(@Name, ''), '<null>');

SELECT ID
FROM dbo.Data
WHERE FREETEXT(*, @Name)

older answer:

One option is to use nullif to convert empty strings to null, followed by isnull to convert nulls to a string with a single space.

SELECT ID
FROM dbo.Data
WHERE FREETEXT(*, isnull(nullif(@Name, ''), ' '))