Recommended way to search hierarchical data MSSQL2008

I have a table with the following contents:

• CategoryID
• ParentID
• Name

I would like to have a search functionality that would search the whole hierarchy, for exmple this is the breadcrumb of a category:

Motorcycles/Japan/Kawasaki/600cc to 800cc/1998-2004

If someone searches for "600cc Kawasaki" I would like the above category to be returned. So the categorypath which has the most matches should return.

At the moment I came up with this:

IF ISNULL(@searchTerm, '') = ''
    SET @searchTerm = '""'
DECLARE @Result T开发者_StackOverflow中文版ABLE (CategoryId int) 

DECLARE CategoryCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT CategoryId, ParentId, Name
FROM Category
WHERE FREETEXT([Name], @searchTerm)
OPEN CategoryCursor
DECLARE @CategoryId int
DECLARE @ParentId int
DECLARE @Name nvarchar(100)

FETCH NEXT FROM CategoryCursor INTO @CategoryId, @ParentId, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @FullPath nvarchar(1000)
    SET @FullPath = @Name

    WHILE @ParentId <> 0
    BEGIN
        SELECT @ParentId = ParentId, @Name = [Name]
        FROM Category 
        WHERE CategoryId = @ParentId

        SET @FullPath = @Name + '\' + @FullPath     
    END

    -- Check if @FullPath contains all of the searchterms
    DECLARE @found bit
    DECLARE @searchWords NVARCHAR(100)
    DECLARE @searchText NVARCHAR(255)
    DECLARE @pos int

    SET @found = 1
    SET @searchWords = @searchTerm + ' '
    SET @pos = CHARINDEX(' ', @searchWords)
    WHILE @pos <> 0
        BEGIN
        SET @searchText = LEFT(@searchWords, @pos - 1)
        SET @searchWords = STUFF(@searchWords, 1, @pos, '')
        SET @pos = CHARINDEX(' ', @searchWords)
        IF @searchText = '' CONTINUE
        IF @FullPath NOT LIKE '%' + @searchText + '%' 
            BEGIN
                SET @found = 0
            BREAK
            END
        END

    IF @found = 1
        INSERT INTO @Result VALUES(@CategoryId)

    FETCH NEXT FROM CategoryCursor INTO @CategoryId, @ParentId, @Name
END

CLOSE CategoryCursor
DEALLOCATE CategoryCursor

SELECT * 
FROM Category 
WHERE categoryID IN (SELECT categoryId FROM @Result)

This will first find all catagorynames which contain any of the searchwords. Problem is, I don't want "600cc" for other brands to return, only the one which is related to "Kawasaki". So next I build the breadcrumb for the current category and see if it contains all of the searchwords.

It works but I think it is ineffective so i'm looking for a better method.

Perhaps storing the complete path as text in a new column and search on that?

I'd suggest using the hierarchyid which is in 2008. You would essentially set your hierarchy like this

/1/ - Root Node /1/1/ - Motorcycles /1/1/1/ - Japan /1/1/1/1/ - Kawasaki /1/1/1/2/ - Honda /1/1/2/ - US /1/1/2/1/ - Harley.

Then you can use the hierarchyid to get the entire tree from your 600cc 1984 kawasaki all the way up to motorcycles.

Here's a code sample from Programming Microsoft SQL Server 2008

CREATE FUNCTION dbo.fnGetFullDisplayPath(@EntityNodeId hierarchyid)  RETURNS varchar(max) AS  
BEGIN    
    DECLARE @EntityLevelDepth smallint    
    DECLARE @LevelCounter smallint    
    DECLARE @DisplayPath varchar(max)    
    DECLARE @ParentEmployeeName varchar(max)    

    -- Start with the specified node    
    SELECT @EntityLevelDepth = NodeId.GetLevel(), 
    @DisplayPath = EmployeeName     
    FROM  Employee     
    WHERE NodeId = @EntityNodeId    

    -- Loop through all its ancestors    
    SET @LevelCounter = 0    
    WHILE @LevelCounter < @EntityLevelDepth 
    BEGIN       
        SET @LevelCounter = @LevelCounter + 1       
        SELECT @ParentEmployeeName = EmployeeName        
        FROM  Employee        WHERE NodeId = (SELECT NodeId.GetAncestor(@LevelCounter)               
            FROM Employee 
            WHERE NodeId = @EntityNodeId)       

        -- Prepend the ancestor name to the display path       
        SET @DisplayPath = @ParentEmployeeName + ' > ' + @DisplayPath    
    END    

    RETURN(@DisplayPath)   
END 

My /1/1/2 representation is the string representation. In the database you'd actually see the hex representation (e.g. 0x79).

There are a few key functions on the hierarchyid.

declare @motorcycleAncestor hieararchyid
select @motorcycleAncestor = nodeId.GetAncestor(1)
from parts 
where Label = 'motorcycle'

select * from Parts
where Node.GetAncestor(1) = @motorcyleAncestor;

This query does a couple things. First, it gets the hierarchy id for the node that contains "Motorcycle" as the label. (I assume the hiearchy field is named 'nodeid' but you can obviously call it whatever.)

Next, it takes this node value and finds all the immediate children of motorcycles (who's ancestor, 1 level up, is the motorcycle node. You can actually specify any value, like GetAncestor(3) would be the ancestor 3 levels up). So in that case, it would find Japan, US, Germany etc.

There is another method, called IsDescendantOf(node). You can use it like this:

declare @motorcycleAncestor hieararchyid
select @motorcycleAncestor = nodeId.GetAncestor(1)
from parts 
where Label = 'motorcycle'

select * from Parts
where Node.IsDescendantOf(@motorcycleAncestor) = 1

This would return all items that are children (of any level) underneath motorcycles. It would actually also include Motorcycles.

You can combine these in different ways. For example, we're using them in an org chart of sorts. We have the ability to show results for a single user, or for a user and his siblings (everyone at the exact same level) and a user and all his descendants.

So I could show your information, or I could show everyone in your department, or I could show everyone in your company.