Dealing with NULL in Contains

I'm using a Contains clause for some data that's getting passed from
Reporting Services and I'm having trouble dealing with NULL, which creates a
"Null or empty full-text predicate." error.

My first thought was to trap it using a coalesce or isnull as in
contains(companynamecolumn,isnull(@report_parameter,'thiswillneverbefound'))

but that didn't work. I also tried using a case within the contains, also
didn't work.

I also tried using the OR as in

contains(companyname,'@report_parameter OR "thiswillneverbefound"')

but it took the "report_parameter" literally and even if I missed with the
string literal to fix it I'm guessing it will still choke on the NULL.

The only other idea I had was to use a case before the contains as in

case when @report_parameter is not null then
contains(companyname,@report_parameter) else contains(companyname,'adfdas')
end

but that's not going to fly.

Any ideas? I'd rather avoid messing with the variable in RS if possible.

D.

Trap the problem before making your fulltext query. E.g.

SET @report_parameter = isnull(@report_parameter,'thiswillneverbefound')

Then your code would simply use:

contains(companyname,@report_parameter)

RLF

"D. Andersen" wrote in message

> I'm using a Contains clause for some data that's getting passed from
> Reporting Services and I'm having trouble dealing with NULL, which creates
> a
> "Null or empty full-text predicate." error.
>
> My first thought was to trap it using a coalesce or isnull as in
> contains(companynamecolumn,isnull(@report_parameter,'thiswillneverbefound'))
>
> but that didn't work. I also tried using a case within the contains, also
> didn't work.
>
> I also tried using the OR as in
>
> contains(companyname,'@report_parameter OR "thiswillneverbefound"')
>
> but it took the "report_parameter" literally and even if I missed with the
> string literal to fix it I'm guessing it will still choke on the NULL.
>
> The only other idea I had was to use a case before the contains as in
>
> case when @report_parameter is not null then
> contains(companyname,@report_parameter) else
> contains(companyname,'adfdas')
> end
>
> but that's not going to fly.
>
> Any ideas? I'd rather avoid messing with the variable in RS if possible.