I use coldfusion to call a stored procedure and this is my question.
I use containstable to get results based on rank, but what I want to do is use 2 dynamic variables, one where the the search string goes in the containstable, and then another one in the where clause.
CREATE PROCEDURE [History].[test2]
@Search nvarchar(4000), @Search2 nvarchar(4000)
SELECT ID, a.RANK
FROM containstable (Entries, (Fulltextsearchstory, Title), @Search) as a, Entries b
WHERE a.[KEY] = b.ID @search2
ORDER BY a.RANK DESC
Where @Search would be something like 'Test' and @Search2 could be anything for a where clause, such as searching other columns with dynamic results. I have a search page that has different advanced search features, such as searching a term and a date, so search2 could be 'AND Year = '2004' AND Month = '4'' or it could be just the year, depending on what they use in the advanced search.