Manipulate optional parameters in the WHERE clause

Question

I'm working on a web-form that receives a string that contains an SQL query statement with SQL parameters. It generates an ASP.NET control for each parameter and then assigns the value of each control to the parameters of the SQL query string that will be used by a SqlCommand to fill a DataAdapter.

I have some problems to manage the optional parameters.

Ex.

1. Pass the string to the page:

string query = "SELECT * FROM Table WHERE Field1=@P1 AND field2=@P2";

2. Generate a web control for each parameter

TextBox P1 = new TextBox();
P1.ID = "P1";
...
TextBox P2 = new TextBox();
P2.ID = "P2";
...
PanelParameters.Controls.Add(P1);
PanelParameters.Controls.Add(P1);

3. On click pass the value of each parameters to the SqlCommand as parameters:

SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@P1", P1.Text);
cmd.Parameters.AddWithValue("@P2", P2.Text);

4. Right here it's OK!

But if a parameter is OPTIONAL how can I manage it?

If P2 was optional, the user could not compile it and in that case I don't want to set P2 = NULL (setting the value of the parameter DbNull.Value), but I'd like that P2 will disappear from the query SQL.

Can you help me?

Note that I can't edit the query that I receive because it contains very complex SQL statements.

Answer 1

Change your query to the minimum:

string query = "SELECT * FROM Table WHERE Field1 = @P1";

Then append the rest with a condition:

if (P2.Text != "")
{
    cmd.Parameters.AddWithValue("@P2",P2.Text);
    query += " AND field2=@P2";
}

Answer 2

You can try using a case expression:

SELECT *
FROM Table
WHERE Field1 = @P1
AND CASE WHEN field2 IS null OR field2 = '' THEN field2 ELSE @P2 END

You would still send the parameter, but if it's null or empty, it will be ignored.