Complex Searching

Hello all,

I have been given the task of adding an option to "Search Top Stories" on an existing full text search we already had. I thought this would be easy, just another content type to throw in, but regretfully top stories are calculated on this site on the fly it seems.

The original search (made back in 2003 I think) did the full text search with various cases for various options. I don't understand the full relevance of all of them but its not really necessary. I thought an easy solution would be to get the IDs of top stories and run the same search and include only if the ID is in the top stories list (how disgustingly inefficient, but I really don't have time to rebuild it all - especially when I dont know where else this SP is used). I get an error in the query analyser:
Server: Msg 156, Level 15, State 1, Line 87
Incorrect syntax near the keyword 'WHERE'.

For the life of me I can't figure out what its complaining about.

This is the SQL for the search on the top stories only (so 2nd half of the union):

SELECT DISTINCT TOP 100 PERCENT
S.SearchID, S.TypeID, S.IndexID, CT.Rank, S.ArchiveDate,
CASE @Group
WHEN 1 THEN S.TypeID
END,
CASE @OrderType
WHEN 1 THEN ArchiveDate
END as a,
CASE @OrderType
WHEN 2 THEN ArchiveDate
END as b,
CASE @OrderType
WHEN 3 THEN Rank
END as c

FROMTT_Articles A
INNER JOINTT_XChannelArticle ON A.Article_ID = TT_XChannelArticle.Article_ID
RIGHT OUTER JOINTT_SearchIndex S
INNER JOINContainsTable(TT_SearchIndex,Content,@SearchTerms) CT ON S.SearchID=CT.[Key]
LEFT OUTER JOIN TT_XChannelArticle XA ON S.IndexID=XA.Article_ID AND S.TypeID IN (2,3,4,5) AND XA.Channel_ID IN (SELECT * FROM @ChannelTable)
LEFT OUTER JOIN TT_PressReleases PR ON S.IndexID=PR.Press_ID AND S.TypeID=1 AND PR.Channel_ID IN (SELECT * FROM @ChannelTable)

WHERE A.ArticleID IN (
SELECT DISTINCT TT_Articles.Article_ID
FROM TT_Articles INNER JOIN
TT_XChannelArticle ON TT_Articles.Article_ID = TT_XChannelArticle.Article_ID
WHERE (TT_Articles.ArticleDate <= GETDATE()) AND (TT_Articles.Approved = 1) AND (TT_Articles.Type = 2) AND (TT_XChannelArticle.Channel_ID IN (SELECT * FROM @ContentTable)))

If you would like I can post the original stored procedure.

@ChannelTable and @ContentTable are comma-delimited lists that have been turned into single field tables.

Whoa what a post, thanks for any help you can give guys. I know the design can really be improved, and I'm sure we will do a rebuild next year.

Tim