Using Full Text Searching with SQL Server

The MurrayBookshop allows users to search for books using SQL Servers Full Text Searching. This works resonably well but could be better.

Currently we use a constructed SQL string that is run via sp_executesql. This is required as we perform paging using SELECT TOP x, where x is a variable based upon the page selected, and the number of records per page. With SQL Server 2005 the SELECT TOP command now allows a variable number of records to be selected so this can then be re-designed to be much simpler.

The other problem is that uses may type anything into the search box. Unfortunatly the full text search does not allow any text to be used to perform the select. To overcome this you need to watch for two errors when using Fill from a SqlAdapter. (base.FillDataSet uses a SqlDataAdapter with the passed in command).

try
{
base.FillDataSet(newData, _BrowseCommand);

return new ProductBrowseResponse(request, newData, (int)_BrowseCommand.Parameters["@TotalPages"].Value, (int)_BrowseCommand.Parameters["@TotalProducts"].Value, ok);
}
catch(SqlException e)
{
if(e.Number == 7619 || e.Number == 7603)
{
return BrowseProductsWithoutSearch(request);
}
else
{
throw new DatabaseTechnicalException("Error browsing for products", Connection.ConnectionString, e);
}
}
A cutdown version of the select command is shown below. This selects the second page of 10 records that match the text in @SearchStringSQL.

SELECT Product.[ProductCode], Product.[ProductTitle], [Rank]
FROM [Product]
INNER JOIN
( SELECT ProductCode, ProductTitle, Rank
FROM
( SELECT TOP 10 ProductCode, ProductTitle, Rank
FROM
( SELECT TOP 20 [Key] As ProductCode, ProductTitle, Rank
FROM FREETEXTTABLE(Product, ProductTitle, @SearchStringSQL) AS KEY_TBL
INNER JOIN Product ON KEY_TBL.[Key] = Product.ProductCode
ORDER BY Rank DESC, ProductTitle ASC
) AS Table1
ORDER BY Rank ASC, ProductTitle DESC
) AS Table2
) AS TABLE3 ON Table3.ProductCode = Product.ProductCode
ORDER BY Rank DESC, Product.ProductTitle ASC;

The BrowseProductsWithoutSearch performs the same query without the full text search.