T-SQL conditional join using full text search

Question

Here is a simplified stored procedure query I'm using with Full Text Search:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        cd.Model            AS Model,
        u.Id                AS UserId,
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY]
    WHERE c.Status = 'Active'

I'm passing in a parameter @SearchTerms, which sometimes could be null or empty. I'm wondering if it is possible to join the CONTAINSTABLE only when the parameter has a value. I tried this:

CASE 
    WHEN (@SearchTerms IS NOT NULL) OR (LEN(@SearchTerms)) > 0
    THEN JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY] 
END

but it didn't work. Not sure if it is possible or my syntax is just wrong?

Answer 1

CASE isn't a control flow element, it's an expression returns a value (which cannot be a boolean). But you can just use normal and/or logic.

WHERE c.Status = 'Active'
  AND (
    @SearchTerms IS NULL OR
    @SearchTerms = '' OR
    EXISTS (SELECT 1
        FROM CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti
        WHERE cd.Id = fti.[KEY]
    )
  );

Having said that, this is likely to be very inefficient in many cases. It's probably better to use dynamic SQL.

DECLARE @sql nvarchar(max) = N'
SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        cd.Model            AS Model,
        u.Id                AS UserId,
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    WHERE c.Status = ''Active''
';

IF @SearchTerms <> ''     -- handles nulls also
    SET @sql += N'
      AND EXISTS (SELECT 1
        FROM CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti
        WHERE cd.Id = fti.[KEY]
    )
';

EXEC sp_executesql @sql,
    N'@SearchTerms nvarchar(4000)',
    @SearchTerms = @SearchTerms;