SQL Server dynamic column list in contains function

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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