Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Building a Stored Procedure

Author  Topic 

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-05-31 : 22:22:31
I am trying to create a SP where I can special how many articles to return (@ArticlesToPull), the search terms against the full text (@SearchTerms) and then the where clause if one is passed (@WhereClause). When I try and save the below SP I get errors on @ArticlesToPull and if I remove that then I get errors on the @WhereClause. Am I not able to do this?



CREATE procedure usp_getFeedDateResults (@ArticlesToPull int, @SearchTerms text, @WhereClause text)
as
SELECT TOP @ArticlesToPull Data_ID, Data_FeedID, Data_Title, Data_Link, Data_Desc, Data_DateCreated, WEIGHT.RANK AS RSSFeedRanking FROM dbo.RSS_Feeds_Data Data INNER JOIN CONTAINSTABLE(dbo.RSS_Feeds_Data, *, 'ISABOUT (@SearchTerms)') WEIGHT ON Data_ID = WEIGHT.[KEY] @WhereClause ORDER BY RSSFeedRanking DESC
GO

Quality NT Web Hosting & Design

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-31 : 23:13:53
TOP @Parm

Isn't supported. Try

SET ROWCOUNT @ArticlesToPull
SELECT Data_ID, Data_FeedID, ...
Go to Top of Page

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-06-01 : 00:08:46
How about the @WhereClause?

Quality NT Web Hosting & Design
Go to Top of Page

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-06-01 : 01:33:14
Sam,

Thanks for your help I was able to get one built (hopefully I did it the correct way). I am posting it here to help other users...


CREATE procedure usp_getFeedDateResults_New (@ArticlesToPull int, @SearchTerms varchar(8000), @WhereClause varchar(8000))
as
DECLARE @Query1 varchar(8000),
@Query2 varchar(8000),
@Query3 varchar(8000)

SET @Query1 = 'SELECT Data_ID, Data_FeedID, Data_Title, Data_Link, Data_Desc, Data_DateCreated, WEIGHT.RANK AS RSSFeedRanking FROM dbo.RSS_Feeds_Data Data INNER JOIN CONTAINSTABLE(dbo.RSS_Feeds_Data, *, ''ISABOUT ('
SET @Query2 = ')'') WEIGHT ON Data_ID = WEIGHT.[KEY] '
SET @Query3 = 'ORDER BY RSSFeedRanking DESC'

SET ROWCOUNT @ArticlesToPull

EXEC (@Query1
+ @SearchTerms
+ @Query2
+ @Query3)
GO


Quality NT Web Hosting & Design
Go to Top of Page
   

- Advertisement -