Parameterizing a full text query in sql server

Question

I have a problem using the sql server full text feature. I'm converting some forum software to use the full text search and I have everything setup and working. My problems are related to full text queries. I have designed a few queries which run as desired when I test them in sql server management studio using the CONTAINS predicate to locate my search results eg:

Select ....
From ..... 
WHERE Contains(p.Message,'" dog food "' ) ......

So this runs fine but how can I parameterize this in a prepared statement? Ideally I would like to be able to run a query with a where clause like:

Select ....
From ..... 
WHERE Contains(p.Message,'" @SearchTerm "' ) ...

or even

WHERE Contains(p.Message,'"@SearchTerm" Near "@OtherSearchTerm" ) ...

But this approach doesn't work because of the double quotes and all. I could build the search term up dynamically in the code but I really need to be using parameters for all user input for security reasons. I have looked at a zillion google results trying to find a solution but can't(Surely this must happen everyone or am I missing something really obvious here and/or it's not possible ). Any Ideas?

Answer 1

Create a stored procedure with parameters, like:

CREATE PROCEDURE [sp_FullTextSearch] 
    @SearchTerm nvarchar(500)
AS
BEGIN
    Select ....
    From ..... 
    WHERE Contains(p.Message, @SearchTerm)
END

Then call it from your code.

HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET