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.