I have a database table in SQL Server 2008 with 5 nvarchar(max) columns. We're using the CONTAINS function to look for text in these columns.
We can look in all five columns using this kind of query:
SELECT * FROM SomeTable ST WHERE CONTAINS( (ST.ColumnA, ST.ColumnB, ST.ColumnC, ST.ColumnD, ST.ColumnE) , '"Strawberry"')
Now we want to search for text for one or more of these columns dynamically. For example, only look in ColumnB and ColumnE. I tried using a CASE statement, but I couldn't.
The only solution I can think of is dynamic SQL, but I'd prefer to avoid this. (The full query is very complicated.) Is there a way to do this without using dynamic SQL?
I just can think in a solution like this, but this doesn't use full text Search... let me know if it is useful for you.
SELECT * FROM SomeTable ST WHERE 1=1 AND ((@colA = '') OR ST.ColumnA LIKE @colA) AND ((@colB = '') OR ST.ColumnB LIKE @colB) AND ((@colC = '') OR ST.ColumnC LIKE @colC) AND ((@colD = '') OR ST.ColumnD LIKE @colD) AND ((@colE = '') OR ST.ColumnE LIKE @colE)
The search_on parameters would be bit (0, 1) so, when set on 1, the search on that column would be activated.
The only way I can think of to avoid using dynamic SQL involves using a temp table and if statements and stored proc. Have a stored proc with parameters for each of the columns that include the text to search that columns for. Create a temp table or table variable to store interim results.
Have five different if statements one for each possible column. In each if insert to the temp table if the variable is not null. something like:
IF @ColA is not null BEGIN INSERT INTO #temp SELECT * FROM SomeTable ST WHERE CONTAINS( (ST.ColumnA) , @ColA) END
At the end select from the temp table to show your result.
This only works well though if you don't have very many columns and it would be unlikely that more will be added. Frankly, the fact that you have multiple columns you need to do full text search for the same search string indicates to me that you may have a basic problem with the database design.
You could UNION the 5 individual cases, then PIVOT and concatenate. I'm not sure it's any better than dynamic SQL.
You would end up with something like:
SET @FindKey = '%B%E%' -- This is your search which field criteria WITH RESULTS1 AS ( SELECT PK, 'A' AS Col FROM SomeTable ST WHERE @FindKey LIKE '%A%' AND CONTAINS(ST.ColumnA, '"Strawberry"') UNION SELECT PK, 'B' AS Col FROM SomeTable ST WHERE @FindKey LIKE '%B%' AND CONTAINS(ST.ColumnB, '"Strawberry"') UNION SELECT PK, 'C' AS Col FROM SomeTable ST WHERE @FindKey LIKE '%C%' AND CONTAINS(ST.ColumnC, '"Strawberry"') UNION SELECT PK, 'D' AS Col FROM SomeTable ST WHERE @FindKey LIKE '%D%' AND CONTAINS(ST.ColumnD, '"Strawberry"') UNION SELECT PK, 'E' AS Col FROM SomeTable ST WHERE @FindKey LIKE '%E%' AND CONTAINS(ST.ColumnE, '"Strawberry"') ) ,RESULTS2 AS ( SELECT PK, ISNULL([A], '') + ISNULL([B], '') + ISNULL([C], '') + ISNULL([D], '') + ISNULL([E], '') AS FoundKey FROM RESULTS PIVOT ( MIN(Col) FOR Col IN ([A], [B], [C], [D], [E]) ) AS pvt ) SELECT * FROM SomeTable INNER JOIN RESULTS2 ON RESULTS2.PK = SomeTable.PK WHERE RESULTS2.FoundKey LIKE @FindKey