Searching Database

i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS

/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))

/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description,           Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName,          3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+          3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+          3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+          3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+          3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)           AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC  

/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,   Product.ImageALT, Artist.ArtistName,          (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) *          CASE              WHEN @Word2 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)          END *          CASE              WHEN @Word3 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)          END *          CASE              WHEN @Word4 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)          END *          CASE              WHEN @Word5 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)          END          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC

/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0

/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0  AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1                     AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN

/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0

/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)

/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')

/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)

/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT

SELECT @Matches = 0

WHILE LEN(@Phrase)>0  BEGIN     SELECT @NextSpacePos = CHARINDEX(' ', @Phrase)     IF @NextSpacePos = 0       BEGIN         SELECT @ExtractedWord = @Phrase         SELECT @Phrase=''       END     ELSE       BEGIN         SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1)         SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos)       END

     IF @SoundexWord = SOUNDEX(@ExtractedWord)       SELECT @Matches = @Matches + 1  END

/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?