SQL Containstable with parameters and stored procedures

In a previous post, I wrote about how to use Examine and Lucene to query data in Umbraco 8; In this post, I will show how to use containstable and freetexttable with parameters in a stored procedure to do a full-text search in SQL server.

To follow along with this post, you will need to have the AdventureWorks database installed.

One of the benefits of full-text search is the speed compared to using 'like' query as we build a full-text index. You can also order the results by rank desc and also do joins on other tables.

For the search criteria, we will pass in the search condition via a parameter.

To start, let's create the full-text index by following the steps below.

  1. Right-click on Production.Product
  2. Hover over Full-Text Index and then select Define Full-Text Index
  3. Click Next
  4. Accept the unique index and click next.
  5. Select the column(s) you want the index on
  6. Language for word breaker, select your chosen language then click next.
  7. Click next again
  8. Create a catalogue and click next
  9. Click next again
  10. Click finish

In this example, I just used all the default settings as it works for this example.

FreeTextTable

For FreeTextTable, enter the code below.

USE [AdventureWorks]
GO
/****** Object:  StoredProcedure [dbo].[sp_FullTextSearchFreeTextTable]    Script Date: 16/07/2020 00:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_FullTextSearchFreeTextTable] 
    @SearchTerm nvarchar(500)
AS
BEGIN
 
	SELECT FT_TBL.Name, FT_TBL.MakeFlag  ,KEY_TBL.RANK  
FROM Production.Product AS FT_TBL   
    INNER JOIN FREETEXTTABLE(Production.Product,  
    Name, 
    @SearchTerm) AS KEY_TBL  
ON FT_TBL.ProductID = KEY_TBL.[KEY]  
ORDER BY RANK DESC;  
END

If we now execute the stored procedure using Headset Ball Bearings, you will see the results below.

FTS Result

Containstable

Now if we change the code and use CONTAINSTABLE we can rerun the code.

USE [AdventureWorks]
GO
/****** Object:  StoredProcedure [dbo].[sp_FullTextSearchContainsTable]    Script Date: 16/07/2020 00:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_FullTextSearchContainsTable] 
   @SearchTerm nvarchar(500)
	
AS
declare @s nvarchar(500)
    set @s='"' + @SearchTerm + '"'
BEGIN
 
	SELECT FT_TBL.Name, FT_TBL.MakeFlag  ,KEY_TBL.RANK  
FROM Production.Product AS FT_TBL   
    INNER JOIN CONTAINSTABLE(Production.Product,  
    Name, @s) AS KEY_TBL  
ON FT_TBL.ProductID = KEY_TBL.[KEY]  
ORDER BY RANK DESC;  
END
FTS Containstable

The example above was straightforward and only used a single column for the full-text search, but it does show how you can quickly set up a full-text search in SQL server.

I hope you found this post helpful