CODEWITHSUNDEEP
sqlsql-server
Jin Yong
Jin Yong

Reputation: 43788

return value that column value found matched with the variables value in MSSQL

I have the following SQL script use to select the records that current_status contains the following variables @isPending, @isInvoiced, @isRework, @isCancelled.

SELECT  *  
FROM    vw_sale_search vss  
WHERE CONTAINS (( @isPending, @isInvoiced, @isRework, @isCancelled), vss.current_status )

However, error message is:

(Incorrect syntax near '@isPending'.)

display when I tried to run the script.

Upvotes: 0

Views: 39

Answers (1)

Eralper
Eralper

Reputation: 6622

The main container, in this case your current_status column should be placed in the first place. Then the item to be searched will take the second place in SQL Server Full-Text CONTAINS function

To searching for more than one word, you can use OR syntax in the CONTAINS function

I created sample fulltext catalog and text table as shown in SQL Fulltext tutorial and prepared fıllowing SQL Select script to use OR with Contains function

declare @isPending varchar(10) = 'Data', @isInvoiced varchar(10) = 'Pool', @txt varchar(100)
set @txt = @isPending + ' OR ' + @isInvoiced 
SELECT * FROM TextTable WHERE CONTAINS (textColumn, @txt)

Upvotes: 1

Related Questions