Using SQL Server 2005 CONTAINSTABLE and FREETEXTTABLE with a variable

I’ve been doing a bunch of work with SQL Server 2005 Full-Text Search lately. 

Let’s say you’re working on an application to manage books in a library and you’ve defined a full-text search index on the LibraryEntry table’s Title column.

Here’s a simple sample for how you’d search for all library entries that contain the word “car”. 

SELECT entry.Id, entry.Title, fulltextSearch.Rank
FROM 
LibraryEntry entry
JOIN
FreeTextTable(LibraryEntry, [Title], ‘car’) fulltextSearch
ON
entry.Id = fulltextSearch.[KEY]

That’s pretty easy — just pass ‘car’ into the FreeTextTable() statement and you’re good.  While we’re at it, let’s pretend that that statement just returned 100 matching rows. 

Ok.  So what if you want to run this query inside of a stored procedure and you want to use a variable for the search string?  The documentation says that it’d be perfectly fine to do that.

CREATE PROCEDURE SearchByTitle
@search nvarchar
AS

SELECT entry.Id, entry.Title, fulltextSearch.Rank
FROM 
LibraryEntry entry
JOIN
FreeTextTable(LibraryEntry, [Title], @search) fulltextSearch
ON
entry.Id = fulltextSearch.[KEY]

Run this by issuing “EXEC SearchByTitle @search = ‘car’” and you should get back 100 records just like the previous query, right? 
Wrong.  You get back zero records.  You don’t get any errors or warnings.  You just get back nothing.

The solution: change the variable declaration from “nvarchar” to “nvarchar(1024)”. 

Here’s the final working version of that stored procedure:

CREATE PROCEDURE SearchByTitle
@search nvarchar(1024)
AS

SELECT entry.Id, entry.Title, fulltextSearch.Rank
FROM 
LibraryEntry entry
JOIN
FreeTextTable(LibraryEntry, [Title], @search) fulltextSearch
ON
entry.Id = fulltextSearch.[KEY]

BTW, this also works for the CONTAINSTABLE() command as well.

-Ben