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.
Russell Fields - 13 Aug 2008 20:17 GMT
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
> 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
[quoted text clipped - 24 lines]
>
> Any ideas? I'd rather avoid messing with the variable in RS if possible.