SQL Search Problem With New SearchContent Function

Question

Hi everyone, I have a question about searching in SQL. We have a piece of code below responsible for searching a set of content within our site. When we wrote the StaticContent algorithm, it worked fine. After adding ContribContent (2nd and 4th par) it does not appear to work. Do you have any idea why?

Thanks so much!

ALTER PROCEDURE [dbo].[SearchContent]
    @PageNumber tinyint,
    @ResultsPerPage 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
    BEGIN
    DECLARE @Content TABLE
    (RowNumber smallint IDENTITY (1,1) NOT NULL,
    ContentID int,
    Title varchar(50),
    UserID int,
    Summary text,
    FirstName varchar(50),
    LastName varchar(50),
    Picture varchar(100),
    Type varchar(50),
    Rank int
)

IF @AllWords = 0
INSERT INTO @Content
SELECT ContribContent.ContentID, ContribContent.Title, ContribContent.UserID,
SUBSTRING(Article, 1, 300) + '...' AS Summary, Users.FirstName, Users.LastName, Users.Picture, 'Contributed Content' AS Type,
3 * dbo.WordCount(@Word1, Title) + dbo.WordCount(@Word1, Article) +
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article) +
3 * dbo.WordCount(@Word3, Title) + dbo.WordCount(@Word3, Article) +
3 * dbo.WordCount(@Word4, Title) + dbo.WordCount(@Word4, Article) +
3 * dbo.WordCount(@Word5, Title) + dbo.WordCount(@Word5, Article)
AS Rank
FROM ContribContent
INNER JOIN Users
ON ContribContent.UserID = Users.UserID
ORDER BY Rank DESC

IF @AllWords = 0
INSERT INTO @Content
SELECT StaticContent.ContentID, StaticContent.Title, StaticContent.UserID,
SUBSTRING(Article, 1, 300) + '...' AS Summary, Users.FirstName, Users.LastName, Users.Picture, 'Static Content' AS Type,
3 * dbo.WordCount(@Word1, Title) + dbo.WordCount(@Word1, Article) +
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article) +
3 * dbo.WordCount(@Word3, Title) + dbo.WordCount(@Word3, Article) +
3 * dbo.WordCount(@Word4, Title) + dbo.WordCount(@Word4, Article) +
3 * dbo.WordCount(@Word5, Title) + dbo.WordCount(@Word5, Article)
AS Rank
FROM StaticContent
INNER JOIN Users
ON StaticContent.UserID = Users.UserID
ORDER BY Rank DESC

IF @AllWords = 1
INSERT INTO @Content
SELECT ContribContent.ContentID, ContribContent.Title, ContribContent.UserID,
SUBSTRING(Article, 1, 300) + '...' AS Summary, Users.FirstName, Users.LastName, Users.Picture, 'Contributed Content' AS Type,
(3 * dbo.WordCount(@Word1, Title) + dbo.WordCount(@Word1, Article)) *
CASE
WHEN @Word2 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word3 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word4 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word5 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END
AS Rank
FROM ContribContent
INNER JOIN Users
ON ContribContent.UserID = Users.UserID
ORDER BY Rank DESC

IF @AllWords = 1
INSERT INTO @Content
SELECT StaticContent.ContentID, StaticContent.Title, StaticContent.UserID,
SUBSTRING(Article, 1, 300) + '...' AS Summary, Users.FirstName, Users.LastName, Users.Picture, 'Static Content' AS Type,
(3 * dbo.WordCount(@Word1, Title) + dbo.WordCount(@Word1, Article)) *
CASE
WHEN @Word2 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word3 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word4 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END *
CASE
WHEN @Word5 IS NULL THEN 1
ELSE
3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
END
AS Rank
FROM StaticContent
INNER JOIN Users
ON StaticContent.UserID = Users.UserID
ORDER BY Rank DESC

SELECT @HowManyResults = Count(*)
FROM @Content
WHERE Rank > 0

SELECT ContentID, Title, Title, UserID, Summary, FirstName, LastName, Picture, Type, Rank
FROM @Content
WHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ResultsPerPage+1 AND @PageNumber * @ResultsPerPage
ORDER BY Rank DESC
END

Answer 1

I am no database expert, but sometimes it helps to step it out.

For example, the second portion of the ContribContent appears as this (I reformatted it for my sake):

IF @AllWords = 1
INSERT INTO @Content
SELECT 
    ContribContent.ContentID, 
    ContribContent.Title, 
    ContribContent.UserID,
    SUBSTRING(Article, 1, 300) + '...' AS Summary, 
    Users.FirstName, 
    Users.LastName, 
    Users.Picture, 
    'Contributed Content' AS Type,
    (
        (3 * dbo.WordCount(@Word1, Title) + dbo.WordCount(@Word1, Article)) *

        CASE 
            WHEN @Word2 IS NULL THEN 1
        ELSE
            3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
        END 

        *

        CASE 
            WHEN @Word3 IS NULL THEN 1
        ELSE
            3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
        END 

        *

        CASE
            WHEN @Word4 IS NULL THEN 1
        ELSE
            3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
        END 

        *

        CASE
            WHEN @Word5 IS NULL THEN 1
        ELSE
            3 * dbo.WordCount(@Word2, Title) + dbo.WordCount(@Word2, Article)
        END
    ) AS Rank
FROM ContribContent
INNER JOIN Users ON ContribContent.UserID = Users.UserID
ORDER BY Rank DESC

After each of these inserts I would recommend doing a PRINT or SELECT of @Content. This will tell you how the data is being left after each phase of the debugging process. Sometimes if the tables you are doing an INNER JOIN do not match correctly (i.e. Nulls or missing values) the expected data will not be returned. Also, whenever dealing with possible null values I like to output the various test data and its results to make sure they are doing what I intend them to (for instance, the CASE structures).