SQL Searching Error in Content

Question

Hi everyone, we are having an issue searching in the ContribContent Function. The StaticContent works fine, but the system will not search for CC.

The issue is this stored procedure is meant to search for content on an informational website there are three datatables involved:

• StaticContent (written by our content team),

  ContribContent (written by others), and

  Users (any type of content is associated to the user who submitted it)

The procedure is meant to search through the content and return the content matching the search terms and rank them in terms of relevency, it will also return the number of pieces of content returned. Currently it will return Static Content just fine, but will not return Contributed Content

Any ideas why? Thank you

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,
            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.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.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