Why can't ISNULL be called inside a CONTAINS statement?

Question

Let's say I have this SQL query:

declare @input varchar(20)
select *
from myTable
where CONTAINS (myColumn, ISNULL(@input, 'replacement text'))

If I try to execute this, I get an error that says "Incorrect syntax near 'ISNULL'." I know that this is the correct syntax for ISNULL. Is there a reason why the ISNULL cannot be called inside of the CONTAINS statement?

Answer 1

A string literal is expected there and not an expression.

Update

The code for the procedure is:

CREATE PROCEDURE freetext_rank_proc
       @select_list             nvarchar(1000),
       @from_table              nvarchar(517),
       @freetext_column         sysname,
       @freetext_search         nvarchar(1000),
       @additional_predicates  nvarchar(500)      = '',
       @order_by_list           nvarchar(500)      = ''
AS 
BEGIN
   DECLARE @table_id              integer,
           @unique_key_col_name   sysname,
           @add_pred_var          nvarchar(510),
           @order_by_var          nvarchar(510) 

   -- Get the name of the unique key column for this table.
   SET @table_id = Object_Id(@from_table)
   SET @unique_key_col_name = 
   Col_Name( @table_id, 
   ObjectProperty(@table_id, 'TableFullTextKeyColumn') )     

   -- If there is an additional_predicate, put AND() around it.
   IF @additional_predicates <> ''
      SET @add_pred_var = 'AND (' + @additional_predicates + ')'
   ELSE
      SET @add_pred_var = ''

   -- Insert ORDER BY, if needed.
   IF @order_by_list <> ''
      SET @order_by_var = 'ORDER BY ' + @order_by_var
   ELSE
      SET @order_by_var = ''

   -- Execute the SELECT statement.
   EXECUTE (   'SELECT ' 
             + @select_list
             + ' FROM '
             + @from_table
             + ' AS FT_TBL, FreetextTable('
             + @from_table
             + ','
             + @freetext_column
             + ','''
             + @freetext_search
             + ''') AS KEY_TBL '
             + 'WHERE FT_TBL.'
             + @unique_key_col_name
             + ' = KEY_TBL.[KEY] ' 
             + @add_pred_var
             + ' '
             + @order_by_var
           )
END

Answer 2

try this:

declare @input varchar(20)
SET @input=ISNULL(@input, 'replacement text')
select *
from myTable
where CONTAINS (myColumn, @input)

it is the same problem when you try to put an expression into a stored procedure call:

EXEC YourProcedure ISNULL(@input, 'replacement text') --will fail too

CONTAIN expects a string literal, see CONTAINS (Transact-SQL)

Answer 3

Because the syntax of the language doesn't allow it:

CONTAINS
   ( { column_name | ( column_list ) | * } 
     ,'<contains_search_condition>'     
   [ , LANGUAGE language_term ]
   ) 

<contains_search_condition> ::= 
    { <simple_term> 
    | <prefix_term> 
    | <generation_term> 
    | <proximity_term> 
    | <weighted_term> 
    } 
    | { ( <contains_search_condition> ) 
    [ { <AND> | <AND NOT> | <OR> } ] 
    <contains_search_condition> [ ...n ] 
    } 

As you can see, the syntax requires exactly a contains_search_condition as described above, between single quotes, not an expression.

Answer 4

AS a guess I would say because contains is used for full-text search not regular t-SQl so regular t-sql functions wouldn't work in it. @KM has posted what I woudl tryinstead.