troubleshooting Question

Containstable Query Question

Avatar of dizzot
dizzot asked on
Microsoft SQL Server
1 Comment1 Solution500 ViewsLast Modified:
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.
Example:

CREATE PROCEDURE [History].[test2]
@Search nvarchar(4000), @Search2 nvarchar(4000)
AS
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros