Best scenario for FREETEXTTABLE
- From: "nospam@xxxxxxxxxx" <stoumann@xxxxxxxxx>
- Date: Sun, 18 May 2008 23:07:31 -0700 (PDT)
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
.
- Follow-Ups:
- Re: Best scenario for FREETEXTTABLE
- From: nospam@xxxxxxxxxx
- Re: Best scenario for FREETEXTTABLE
- Prev by Date: RE: Nested Stored Procedure
- Next by Date: Re: Best scenario for FREETEXTTABLE
- Previous by thread: RE: Why do I meet "Word breaking timed" out error
- Next by thread: Re: Best scenario for FREETEXTTABLE
- Index(es):