none
CONTAINSTABLE error "Null or empty full-text predicate" when empty value is passed in

    Question

  • Hello Everyone,
    We are integrating FT searching in our existing sps. most of them look like (structurally):

    WITH CTE AS
    (
    Select ROW_NUMBER() .. [rn],
    xxx [many columns]
    from [many tables and joins]
    inner join
    CONTAINSTABLE(table, column, @searchText) as b
    ON [join statement]
    where [lot of logic]
    )


    select rowxxx
    from CTE
    WHERE rn BETWEEN ...

    problem is when @searchText is empty SQL server throws error "Null or empty full-text predicate". that gives us lot of problem. I am not sure how to tackle this problem efficiently. Instead of inner join in FT table I can use left join but performance goes down dramatically. So, we dont want to use left join. So we decided this:

    IF @searchText <> '' /* checking input value */
    BEGIN

    WITH CTE AS
    (
    Select ROW_NUMBER() .. [rn],
    xxx [many columns]
    from [many tables and joins]
    inner join
    CONTAINSTABLE(table, column, @searchText) as b
    where [lot of logic]
    )


    select rowxxx
    from CTE
    WHERE rn BETWEEN ...

    END
    ELSE
    BEGIN
    WITH CTE AS
    (
    Select ROW_NUMBER() .. [rn],
    xxx [many columns]
    from [many tables and joins]
    where [lot of logic]
    )

    /* in the above query no mention of ft search because we want everything */

    select rowxxx
    from CTE
    WHERE rn BETWEEN ...

    END

    problem is it impossible to manage such sp. therefore, I am wondering what is the best way to handle this scenario?

    Thank you
    Wednesday, May 21, 2008 4:43 AM

Answers

  • You can use sp_executesql to execute it dynamically while protecting against SQL Injection:

     

    Code Snippet

    declare @sql nvarchar(max)

    select @sql = 'WITH CTE AS

    (

    Select ROW_NUMBER() .. [rn],

    xxx [many columns]

    from [many tables and joins]' +

    case when isnull(@searchText, '') != ''

    then '

    inner join

    CONTAINSTABLE(table, column, @searchText) as b'

    else '

    where [lot of logic]

    )

     

    select rowxxx

    from CTE

    WHERE rn BETWEEN ...

    ' END

    DECLARE @ParamsString nvarchar(1000)

    SET @ParamsString = N'@searchText varchar(100)'

     

    sp_executesql @sql,

    @ParamsString,

    @searchText = @searchText

     

     

    I am afraid those are your only two options.  Maintain the code as you wrote it, which may have performance issues artificially due to the plan that gets cached when this first executes, or use Dynamic SQL with sp_executesql to call the code in a safe manner.  If it calls the CONTAINSTABLE part of the query first, it could cache a plan that is not optimal for your other non-CONTAINSTABLE query, and vice versa.
    Wednesday, May 21, 2008 5:43 AM
    Moderator

All replies

  • You can use sp_executesql to execute it dynamically while protecting against SQL Injection:

     

    Code Snippet

    declare @sql nvarchar(max)

    select @sql = 'WITH CTE AS

    (

    Select ROW_NUMBER() .. [rn],

    xxx [many columns]

    from [many tables and joins]' +

    case when isnull(@searchText, '') != ''

    then '

    inner join

    CONTAINSTABLE(table, column, @searchText) as b'

    else '

    where [lot of logic]

    )

     

    select rowxxx

    from CTE

    WHERE rn BETWEEN ...

    ' END

    DECLARE @ParamsString nvarchar(1000)

    SET @ParamsString = N'@searchText varchar(100)'

     

    sp_executesql @sql,

    @ParamsString,

    @searchText = @searchText

     

     

    I am afraid those are your only two options.  Maintain the code as you wrote it, which may have performance issues artificially due to the plan that gets cached when this first executes, or use Dynamic SQL with sp_executesql to call the code in a safe manner.  If it calls the CONTAINSTABLE part of the query first, it could cache a plan that is not optimal for your other non-CONTAINSTABLE query, and vice versa.
    Wednesday, May 21, 2008 5:43 AM
    Moderator
  • Hi Jonathan,

    Thank you for the reply. I was also thinking of using sp_executesql. But i am disappointed that microsoft didnt provide us other options. I wonder why microsoft put these half baked features in their product.

    first clarification i need, If i use sp_executesql, as far as i know, sql server wont be able to cache any plan upfront so everytime i run the main sp the overall execution time wont be optimal. Is that true in this case?

    Second clarification, are you suggesting that if we keep our code as the "original" way then the server may cache only one execution plan which may be helpful in one scenario but rather slow in other scenario?

    Thank you
    Wednesday, May 21, 2008 11:31 PM
  • For the first:

     

    Since the dynamic string is parameterized, then both of the resulting selects will be cached in the plan cache.  See Erland Sommarsmok's article on Dynamic Search Conditions.  The drawback to this is that the calling user must have access to SELECT from the table.  This is the only drawback that I know of to using this form of dynamic SQL.

     

    For the Second:

     

    Interestingly enough it is also covered in the same article above under the Static SQL subsection.  However, I may have been premature with my statement since you are only showing one parameter, you might be ok.  On complex searches with multiple if else paths, this can be problematic.

     

     

    Wednesday, May 21, 2008 11:52 PM
    Moderator