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 2005 Forums  Transact-SQL (2005)  Best appoach for FREETEXT or CONTAINS

Author  Topic  Mazzo Starting Member 1 Post Posted - 2008-05-18 : 05:43:35 Hi, I'm having some difficulties as to which approach is the right one in this scenario: I have a table of books (tblShopItem), a table of categories (authors, series etc. = tblShopCategory) and a table that connects these (tblShop_Item_Category_Ref), so one book can have multiple authors, series etc. When I recieve the data, I recieve the list of categories (and category types) for one book via a UDF as a comma-delimited list, so I only get one line per book in the resultset. My problem is that I can't use FREETEXT or FREETEXTTABLE properly in this context, because I want to search both the Book-table and the Category-table, so I find books that matches one or the other instead of books that matches the criteria in BOTH tables! Also, the @SearchString paramter should be optional which I don't know how to do when using JOINs. I've tried to create a view of the SELECT-statement, but are not allowed to create a unique index because of the UDF - But maybe my approach is wrong - any suggestions would be appreciated! Best, Mazzo Here's the code: ALTER PROCEDURE [dbo].[usp_SearchAll] ( @PageIndex int, @PageSize int, @SearchString nvarchar(255) = NULL, @CategoryID int = NULL ) AS SET NOCOUNT ON; WITH SearchTable AS ( SELECT ROW_NUMBER() OVER (ORDER BY si.ItemName) AS ROW, si.ID, si.tblShopProductType_ID, si.ItemName, si.ShortDescription, si.Issue, si.Price, si.CampaignPrice, si.CampaignStartDate, si.CampaignEndDate, sir.Rating, dbo.GetCategories(si.id) AS Categories FROM tblShopItem si JOIN tblShopItem_Category_Ref scr ON scr.tblShopItem_ID = si.ID LEFT JOIN tblShopItemReview sir ON sir.tblShopItem_ID = si.ID JOIN FREETEXTTABLE(tblShopCategory, *, @SearchString) AS tblKeyShopCat ON scr.tblShopCategory_ID = tblKeyShopCat.[KEY] --JOIN FREETEXTTABLE(tblShopItem, *, @SearchString) AS tblKeyShopItem ON si.ID = tblKeyShopItem.[KEY] WHERE scr.tblShopCategory_ID = COALESCE(@CategoryID, scr.tblShopCategory_ID) AND GetDate() BETWEEN si.StartDate AND si.EndDate ) SELECT SearchTable.*, (SELECT MAX(ROW) FROM SearchTable) AS RC FROM SearchTable WHERE ROW BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize SET NOCOUNT OFF