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.

For FreeTextTable, enter the code below:

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


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

This time, we only see one results returned.


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, if yes please leave a comment below.

Leave a comment