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?

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

score 5 · Answer 1 · answered Apr 11 '13 at 10:10

5

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";
}

answered Apr 11 '13 at 10:10

Luke Marlin

1,398
9
26

Thank you it's a very good idea but i can't change the query and then append because the query is received via an XML file that i read and often it is very difficult to decompose because of the complexity of the SQL statement. – Teo Apr 11 '13 at 12:53
You should update your question to show this. The only solution I see then is a component that analyse the received query and reconstruct it. – Luke Marlin Apr 11 '13 at 13:00

score 2 · Answer 2 · answered Apr 11 '13 at 10:12

2

You can try using case statement:

SELECT * FROM Table WHERE Field1=@P1 AND CASE WHEN field2= 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.

answered Apr 11 '13 at 10:12

Arijus Gilbrantas

349
3
3

Thank you, very great method but I can't modify the query that i receive because the statement is often very complex and i can't find with precision every places where to insert the CASE STATEMENT parts... – Teo Apr 11 '13 at 12:58
Can you run the query after receiving it? Hold current values in memory and then if value is not null in textbox, replace with the new one. If textbox is empty, put the value you pulled previously. – Arijus Gilbrantas Apr 15 '13 at 09:55

Manipulate optional parameters in the WHERE clause

2 Answers2