Search function not working as fully intended

62 views Asked by At

I have created a stored procedure in SQL Server:

ALTER PROCEDURE [dbo].[People_SearchByValue]
    @strFind AS NVARCHAR(MAX)    
AS
/*
EXEC People_SearchByValue @strFind = m
*/
BEGIN
    SET NOCOUNT ON;

    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Title Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Bio Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Headline Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Summary Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Slug Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE StatusId Like @strFind
    END
    BEGIN
        SELECT Title, Bio, Headline, Summary, Slug, StatusId, Skills 
        FROM PeopleTable
        WHERE Skills Like @strFind
    END
END

It basically lets me set a search variable and will return a person with that variable in one of their columns.

However as I am calling this procedure in C# it works completely fine but only when I search for the Title input, does not work for any other column.

namespace TestingOnceAgain
{
    class Program
    {
        static void Main(string[] args)
        {
            var command = new SqlCommand("People_SearchByValue");
            command.CommandType = CommandType.StoredProcedure;

            Console.WriteLine("What are you searching for?");
            command.Parameters.Add("@strFind", SqlDbType.NVarChar, 100).Value = Console.ReadLine();

            using (SqlConnection PubsConn = new SqlConnection("Server=.\\SQLEXPRESS;Database=PeopleDatabase;Trusted_Connection=True;"))
            {
                PubsConn.Open();
                command.Connection = PubsConn;
                SqlDataReader myReader = command.ExecuteReader();

                while (myReader.Read())
                {
                    Console.WriteLine("Title: {0} Bio: {1} Headline: {2} Summary: {3} Slug: {4} StatusId: {5} Skills: {6}", 
                        myReader.GetString(0), 
                        myReader.GetString(1), 
                        myReader.GetString(2), 
                        myReader.GetString(3),
                        myReader.GetString(4),
                        myReader.GetSqlInt32(5),
                        myReader.GetString(6));
                }              
            }

            Console.ReadLine();
        }
    }
}

If I try searching for a field in bio or any other column it writes nothing.

0

There are 0 answers