2

Is there a way to avoid the error Null or empty full-text predicate in the example below ?

DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''

SELECT * FROM   myTable
WHERE
    /* 
    SOME CONTITIONS 
    AND
    */ 
    ( 
      @SearchText = ''
      OR
      (
        @SearchText <> ''
        AND
        CONTAINS((myField1, myField2), @SearchText)
      )
    )

I could be doing like this, but I want to avoid duplicating the code :

DECLARE @SearchText VARCHAR(1000);
SET @SearchText = ''

IF @SearchText = ''
BEGIN
  SELECT * FROM   myTable
  WHERE 
      /* 
      SOME CONTITIONS 
      */ 
END
ELSE
BEGIN
  SELECT * FROM   myTable
  WHERE
      /* 
      SOME CONTITIONS 
      AND
      */ 
      ( 
        @SearchText = ''
        OR
        (
          @SearchText <> ''
          AND
          CONTAINS((myField1, myField2), @SearchText)
        )
      )
END

[EDIT]

I found the answer here

So the solution is to set the @SearchText to '""' instead of leaving it empty.

Community
  • 1
  • 1
leoinfo
  • 7,860
  • 8
  • 36
  • 48
  • The search for a "match all" pattern led to [this](http://stackoverflow.com/questions/506284/containstable-and-contains-which-string-to-pass-to-match-all-records). – HABO Feb 09 '12 at 19:38
  • A default pattern like `'"a*" or "b*" or ...'` matches most words. There is still the opportunity to lose rows that only contain noise words, numbers, ... . – HABO Feb 09 '12 at 19:45
  • @user\d* - The idea is that when I have nothing to search for (@SearchText=""), I get an error (Null or empty full-text predicate). I have a lot of other filters there in WHERE clause, so I do not want to duplicate all that logic. I was hoping I can avoid that... – leoinfo Feb 09 '12 at 20:14

1 Answers1

3

I found the answer here.

The solution is to set the @SearchText to '""' instead of leaving it empty.

Pang
  • 9,564
  • 146
  • 81
  • 122
leoinfo
  • 7,860
  • 8
  • 36
  • 48