Dynamic where clause in parameter

Question

I am currently trying to build up the where clause of an SqlCommand.

something similar to this

myCommand.CommandText = "SELECT * " +
                        "FROM TABLE1 " + 
                        "@whereClause";
//I build up the where clause with a StringBuilder
myCommand.Parameters.AddWithValue("@whereClause" theClause.ToString());

But it doesn't seem like this is possible. I got the exception :

SqlException Incorrect syntax near '@whereClause'

The reason I want to do something like this is because I want to avoid X call to the database and this way I leave sorting and filtering to the server.

Is there anyway to do something similar to this?

/edit : The where clause would look something like this WHERE (TABLE1.COL1 = 'the value' OR TABLE1.COL1 = 'another value' OR TABLE1.COL1 = 'this value' ... )

/edit Finaly this was due to a stupid typo error... after I changed to not use the parametrize query. I'll upvote those answer who helped my out. I will mark as answer what was to closer to fix my situation even if it didn't fixed my (stupid) bug

The @whereClause will probably quoted, because it is a string. Why don't you use `myCommand.CommandText = "SELECT * " + "FROM TABLE1 " + theClause.ToString();` Also, is `theClause` prefixed with `WHERE`? — Jeroen van Langen, Oct 01 '13 at 14:03
I don't understand the relation between dynamic sql and _"I want to avoid X call to the database and this way I leave sorting and filtering to the server"_. — Tim Schmelter, Oct 01 '13 at 14:04
@Jeroen I'd recommend against building SQL strings like this because it increases the risk of SQL injection — Andrew, Oct 01 '13 at 14:04
@Andrew, Normally for parameters yes. But these aren't parameters so the responsible for the query-layout lays elsewhere. — Jeroen van Langen, Oct 01 '13 at 14:08

score 1 · Answer 1 · answered Oct 01 '13 at 14:04

You can't use a clause (where) with parameters, you are only allowed to use parameters with command.Parameters.

To build a dynamic Where clause, you have to build your query based on conditions and string concatenation and then add the parameters accordingly.

Something like:

sb.Append("SELECT * FROM TABLE1 ");

if (someCondition)
{
    sb.Append("WHERE XColumn = @XColumn");
    myCommand.Parameters.AddWithValue("@XColumn", "SomeValue");
}
else
{
    sb.Append("WHERE YColumn = @YColumn");
    myCommand.Parameters.AddWithValue("@YColumn", "SomeOtherValue");
}
myCommand.CommandText = sb.ToString();

score 1 · Accepted Answer · answered Oct 01 '13 at 14:11

It seems you're trying to add the entire WHERE clause as a parameter - that won't work!

So suppose you need to build something like this

SELECT * from TABLE1 WHERE Field1=@Field1Value and Field2=@Field2Value

And assuming

you have a List<WhereField> of fields to include in the WHERE clause
and that all clauses are ANDed together
and WhereField looks something like this

public class WhereField
{
   public string FieldName{get;set;}
   public object FieldValue{get;set;}
   public string ComparisonOperator{get;set;}
}

then you have something like this:

var whereClause = new StringBuilder();
    foreach (var field in WhereFields)
    {
     whereClause.Append(field.FieldName)
     .Append(field.ComparisonOperator)
     .Append("@")
     .Append(field.FieldName).Append("Value")
     .Append (" AND ");
//add the parameter as well:
    myCommand.Parameters.AddWithValue("",field.FieldName+"Value");
    }
 //cleanly close the where clause
whereClause.Append("1=1");

And now you can execute

myCommand.CommandText = "SELECT * " +
                        "FROM TABLE1 WHERE " + whereClause.ToString();

since I alway test for the same field I'm not sure this would work Check my question edit to see what the where clause would look like — Rémi, Oct 01 '13 at 14:16
@im_a_noob you can just modify the sample code above to append " OR " instead of " AND " and in that case each object in the List (which is just an example of how you *might* represent this data) would have the same FieldName property - bear in mind it wasn't meant as a copy-paste solution, just an example of how you might go about building the clause. The main point was to not try to put the entire WHERE clause into a parameter - you need to have two separate parts - the SQL and the actual parameters themselves... — Stephen Byrne, Oct 01 '13 at 14:27

score 0 · Answer 3 · answered Oct 01 '13 at 14:04

0

May be you need

myCommand.CommandText = "SELECT * " +
                        "FROM TABLE1 WHERE " + 
                        "@whereClause";

answered Oct 01 '13 at 14:04

Microsoft DN

9,706
10
51
71

What if `@whereClause` has also `WHERE` ? – Soner Gönül Oct 01 '13 at 14:05
Exactly this is my question what does theClause.ToString() returns!! – Microsoft DN Oct 01 '13 at 14:06

Dynamic where clause in parameter

3 Answers3

Linked