sql server conditional where clause big trouble

Question

2Im in a big trouble, im refactoring a very big procedure to full text search, and I found a very important outer apply:

select top 1 *
from tablea
where
 column1 like
  case
   when @value = 1 or @value = 2 then '%' + @something + '%'
   else '%'
  end

The logic here is: if @value is 1 or 2, get the first register with @something inside. Else, get any register.

What I need now is:

select top 1 *
from tablea
where
  case
   when @value = 1 or @value = 2 then contains(column1, @something)
   else 1 = 1
  end

The code above dont work, its mal formed, and I have no clue how solve that.

dcp · Accepted Answer · 2011-11-11T15:46:24.917

1

select top 1 *
from tablea
where
   (@value IN (1,2) AND (@something = '""' OR contains(column1, @something)))
 OR @value NOT IN (1,2)

edited Nov 11 '11 at 15:46

answered Nov 10 '11 at 17:17

dcp

1

Omg! In my face! Thank you very much (just waiting the time to mark awnser) – Nov 10 '11 at 17:21
man, its not working, because something is null when value is not in (1, 2), but sql server preparses it anyways and I get a null predicate error. – Nov 10 '11 at 17:47
the predicate '' is a mal formed predicate too. and sql server parses it anyways. =\ – Nov 11 '11 at 15:40
1

ok, let's try again with my latest edit :). If you still get the error, the next thing to try would be a union, but I think that would cause problems with using TOP. Refer to this link for some more hints that may be helpful: http://stackoverflow.com/questions/189765/7645-null-or-empty-full-text-predicate – dcp Nov 11 '11 at 15:47

1 Answers1