Search code examples
sqlsql-serverparameterscontainsfreetext

How to use parameters in contains method in store procedure

I am testing contains method in store procedure. I would like to see data result like:

Example Result for John:

  • John Nick
  • Papa John
  • Harly John Fredy

I don't want to use LIKE method. Can we use starts with or ends with (*) operator with parameters in SP?


CONTAINS(name,@name) // query is working

but if i can try like this:

CONTAINS(name, '"john" OR "john*"') // query is working
CONTAINS(name,'"@name" OR "@name*"') // query is not working

With parameters same query is not working in SP. Is it posibble to do this in SP?

Thanks,

Solution

  • With CONTAINS, in order to pass a wildcard through with a variable, you need to make the wildcard a part of the variable.

    The easiest way to do it within a stored procedure is adding a variable (or modifying your current variable).

    For example, 

    DECLARE @nameX NVARCHAR(4000) = '"' + @name + '" OR "' + @name + '*"'
...
CONTAINS(name, @nameX)