Your Stored Procedure should be like this
It will accept 2 Parameters
1. Flag - Which type of value passed
2. Value- What Value passed
Create Procedure dsp_GridData
(
@flag VARCHAR(10),
@Value VARCHAR(10)
)
BEGIN
SET NOCOUNT ON
IF @flag = 'ID'
BEGIN
SELECT desired_field FROM YourTable where id = CONVERT(int,@value)
END
IF @flag = 'Country'
BEGIN
SELECT desired_field FROM YourTable where country = @value
END
AND so on..........
SET NOCOUNT OFF
END
In your C# code
protected void ddl_SelectedIndexChanged(object sender, EventArgs e)
{
string flag;
if(ddlList.SelectedValue == "ID")
{
flag = "ID";
}
and so on for all other flag
string cn = ConfigurationManager.ConnectionStrings["connectivity"].ConnectionString;
using (SqlConnection connection = new SqlConnection(cn))
{
SqlCommand command = new SqlCommand("dsp_GridData", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@flag", flag);
command.Parameters.AddWithValue("@value", TextBox1.Text);
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
Remember following points
1. Never Use Select * in any stored procedures
2. Instead of creating 4 - 5 stored procedure include all in 1