2

Is there a way for CONTAINS to have, Column names as both parameters like

CONTAINS(cars.brand,COALESCE(@brand,cars.brand))

Now if @brand = NULL the second parameter will be cars.brand making the query as

CONTAINS(cars.brand,cars.brand)

which will help me in ignoring the case where I will be looking for a particular brand in a table as in 

WHERE 1=1

and will return the result no matter what the brand is .

But this fails with a syntax error as the second parameter has to be string .

Keith
  • 20,636
  • 11
  • 84
  • 125
pcraft
  • 156
  • 1
  • 10

1 Answers1

2

One of the ways this is done is

((@brand IS NULL) OR CONTAINS(cars.brand, @brand))

passing NULL for a parameter that you do not want to filter by.

[NOTE: if you have many parameters you should be aware of parameter sniffing and the possibility of incorrect cached query plans. Although this might have different behaviour for free-text search]

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Thanks WOW!it worked !so no way to work it through COALESCE ? and how to resolve parameter sniffing or query plans ?COALESCE may be ?because I do have a lot of could-be null parameters . – pcraft Apr 10 '11 at 12:55
  • 1
    parameter sniffing could still be an issue with COALESCE – Mitch Wheat Apr 10 '11 at 12:57