Q:

Select with Contains parameter not working with null

With my select below, if blank string is passed in I get the following error: Null or empty full-text predicate
in my DBAdapter when fetching rows from the database. If I provide a value, such as Well, I do not get results when I should as Well is in the r.[Desc] column. If I pass in Well One, I get: Syntax error near 'one' in the full-text search condition 'Well one'.
If I pass in One, I get nothing.
I've read similar questions here and have not seen a pattern where the value passed in can be nothing, the beginning of the column data, a word in the middle of the column data or more than one word in any order of the column data. I thought Contains returns the row if the column contains the value or part of the value passed in.
What am I doing wrong?
if @Drawing = ''
set @Drawing = null

if @ItemName = ''
set @ItemName = null

if @CF3 = ''
set @CF3 = null

if @Desc = ''
set @Desc = null

if @Design = ''
set @Design = null

if @MaxPSI = 0
set @MaxPSI = null
Select distinct
  ,r.[DRAWING]
  ,r.[DESC]
  ,r.[OP_PSI]
  ,r.[MAX_PSI]
  ,r.[MAX_TEMP]
  ,r.[Insulated]
  ,r.[DESIGN]
From Ref r

inner join Eng e on e.[DRAWING] = r.[DRAWING]

where r.SurveyNumber = @SurveyNumber
And (rtrim(@Drawing) is NUll or rtrim(r.DRAWING)  like rtrim(@Drawing) + '%')
And (rtrim(@Design)  is NUll or rtrim(r.DESIGN)  like rtrim(@Design) + '%')
And (rtrim(@MaxPSI)  is NUll or rtrim(r.MAX_PSI)  like rtrim(@MaxPSI) + '%')
And (rtrim(@CF3)  is NUll or rtrim(e.CF3)  like rtrim(@CF3) + '%')
And (rtrim(@ItemName)  is NUll or rtrim(e.ITEM_NAME)  like rtrim(@ItemName) + '%')
AND  ((@Desc = '""') OR CONTAINS( (r.[Desc]), @Desc))

A:

I think you can try checking for empty as follows:
AND  ((@Desc = '""' OR @Desc = '') OR CONTAINS( (r.[Desc]), @Desc))

I suspect the empty predicate may be getting passed as '' instead of "".
Have not used Contains much but from section in the doc [here][1] it seems you either need operators between the words or need to wrap expression in double quotes "". So what you can do is try passing params like this:
AND  ((@Desc = '""' OR @Desc = '') OR CONTAINS( (r.[Desc]), '"'+@Desc+'"'))

