dynamic query generated by stored procedure or function

I have a grid with 4 columns i.e title, description, keyword, date in C#.net with search button and textbox.

When I write title+description+keyword+date it will return the related data by clicking search button.

If I write keyword+date +keeping description and title null it will return related data from grid. And so on other combination's of this 4 column.

Th开发者_高级运维at is, it's searching from fields in each column and combination changes dynamically

I want a stored procedure or function for it in SQL SERVER. Any suggestion?.. thanks in advance

you can create a procedure with four parameters (each for your options) and following SQL

CREATE PROC Search(
  @title VARCHAR(20) NULL, ...
AS SELECT * FROM your_table WHERE (title = @title OR @title IS NULL) etc.

Anyway, you should parse the input to get values for each parameter. So when you pass null to @title parameter the WHERE condition (title = @title OR @title IS NULL) will be always true.

Make a function in C#, pass it string array containing column names and table name, this function will return the query string.

public string BuildQuery(string[] columnNames, string tableName)
{
string query = string.Empty;

query = "SELECT ";
foreach(string str in columnNames)
query += str + ", ";
query = query.Substring(0, query.Length - 1);
query += " FROM " + tableName;

return query;
}