SQL Server search multiple columns with FREETEXTABLE

Question

GOAL: to return a result based on a search of 3 different columns, each with their own search string value and order by RANK, if possible.

RULES:

1. Allow any column to be searched
2. Results must contain all search strings on their respective column, unless null is passed
3. IF all search strings are null, return empty results

Current stored procedure:

I was able to come up with this after much research in order to abide by the rules stated above.

ALTER PROCEDURE [Application].[usp_Institution_Search] 
    @RowCount INT = 100,
    @Name NVARCHAR(255), 
    @City NVARCHAR(255),
    @Country NVARCHAR(255) 
AS
BEGIN
    SET NOCOUNT ON;

    IF ISNULL(@Name, '') = '' SET @Name = '""' 
    IF ISNULL(@City, '') = '' SET @City = '""' 
    IF ISNULL(@Country, '') = '' SET @Country = '""' 

    SELECT TOP (@RowCount) 
        [i].* 
    FROM 
        [dbo].[Institutions] [i] 
    WHERE 
        ((NULLIF(@Name, '""') IS NULL OR FREETEXT([i].[InstitutionName], @Name)) 
        AND (NULLIF(@City, '""') IS NULL OR FREETEXT([i].[City], @City)) 
        AND (NULLIF(@Country, '""') IS NULL OR FREETEXT([i].[Country], @Country)) 
        AND 
        (
             NULLIF(@Name, '""') IS NOT NULL 
             OR NULLIF(@City, '""') IS NOT NULL 
             OR NULLIF(@Country, '""') IS NOT NULL)
        ) 
END

Problems: I was about to order the results by InstitutionName but then I thought about using a RANK instead. After more research I found that it may be best to use FREETEXTTABLE. At this point, I'm not sure how best to work with my situation as most results always talk about searching one string against multiple columns instead.

I'm not totally sure how this works, but I suppose I would like the highest combined RANK from the search of all columns.

If this is possible, please show me some examples.

Answer 1

So after a lot of testing I think I like this result. I'm INNER JOINing the required column (InstitutionName) and LEFT OUTER JOINing the others. Then combine their ranks in the ORDER BY.

I'm not sure if it's as efficient as possible. If anyone has any input, I'd love to hear from you as I always like to learn more.

ALTER PROCEDURE [Application].[usp_Institution_Search] 
    @RowCount INT = 100,
    @Name NVARCHAR(255), 
    @City NVARCHAR(255),
    @Country NVARCHAR(255) 
AS
BEGIN
    SET NOCOUNT ON;

    IF ISNULL(@Name, '') = '' SET @Name = '""' 
    IF ISNULL(@City, '') = '' SET @City = '""' 
    IF ISNULL(@Country, '') = '' SET @Country = '""' 

    SELECT TOP (@RowCount) 
        [i].* 
    FROM 
        [dbo].[Institutions] [i] 
        INNER JOIN FREETEXTTABLE([Institutions], [InstitutionName], @Name) AS [ft1] ON [ft1].[Key] = [i].[InstitutionId] 
        LEFT OUTER JOIN FREETEXTTABLE([Institutions], [City], @City) AS [ft2] ON [ft2].[Key] = [i].[InstitutionId] 
        LEFT OUTER JOIN FREETEXTTABLE([Institutions], [Country], @Country) AS [ft3] ON [ft3].[Key] = [i].[InstitutionId] 
    ORDER BY 
        [ft1].[Rank] + ISNULL([ft2].[Rank], 0) + ISNULL([ft3].[Rank], 0) DESC 
        , [i].[InstitutionName] ASC 
END