Parameterized query with dynamic where clasue

Question

So I have this query:

var comm = @"SELECT * FROM `TABLE` ";

bool hasWhere = false;

if ( model.Serial > 0 ) {
    comm += " WHERE `SERIAL` LIKE '%" + model.Serial + "%' ";
    hasWhere = true;
}

if ( model.Id.HasValue && model.Id.Value > 0 ) {
    if ( !hasWhere ) {
        comm += " WHERE `NUIP` LIKE '%" + model.Id.Value + "%' ";
        hasWhere = true;
    } else
        comm += " AND `NUIP` LIKE '%" + model.Id.Value + "%' ";
}

if ( model.Date.HasValue ) {
    if ( !hasWhere ) {
        comm += " WHERE `DATE` = '" + model.Date.Value + "' ";
        hasWhere = true;
    } else
        comm += " AND `DATE` = '" + model.Date.Value + "' ";
}
....
....
....

I've read about parameterized queries against SQL Injection and so on. The thing is, given that I'll have a dynamic number of WHERE clauses (based on the search model),how can I parameterize the query? I can't put WHERE a = @A AND b=@B... because the user must not need to search based on all the columns.

Any idea? thanks in advance.

P.S: Can't use LINQ or something similar to that (-business rules-).

The business rules dictate the implementation? Those don't sound like business rules. — George Stocker
– George Stocker, Commented Jun 11, 2012 at 1:22

fenix2222 · Accepted Answer · 2012-06-11 01:17:23Z

3

You can still use SQL parameterized query with where close (WHERE will be sort of dynamic). For example I have paramater @SerialNum that is NULL and I have a parameter @Code that equals to 455.

SELECT
     Column1
    ,Column2
FROM 
     YourTable
WHERE
    (
        @SerialNum IS NULL
        OR
        Column3 LIKE '%' + @SerialNum + '%'
    )
    AND
    (
        @Code IS NULL
        OR
        Column4 LIKE '%' + @Code + '%'
    )

answered Jun 11, 2012 at 1:17

fenix2222

4,7504 gold badges39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

goat Over a year ago

this is the easiest way, avoiding if statements in the program code.

Preet Sangha Over a year ago

Personally I think this better than my way as it is all in the SQL engine! But can you update the code for MYSQL?

Erre Efe Over a year ago

@fenix2222 Brilliant! Absolutely brilliant. Thanks!

Preet Sangha · Accepted Answer · 2012-06-11 01:22:39Z

1

I use this trick.

....
WHERE 1 = 1 
AND a = a 
AND b= @b 
AND c = c 
... etc....

ie I compare the column to it self if I don't want to search

var comm = @"SELECT * FROM `TABLE` WHERE 1 = 1 ";  

if ( model.Serial > 0 ) {  
    comm += " AND  `SERIAL` LIKE '%" + model.Serial + "%' ";  
}  else  {
      comm += " AND  `SERIAL` = `SERIAL`";
}


if ( model.Id.HasValue && model.Id.Value > 0 ) {    
    comm += " AND  `AND` LIKE '%" + model.Id.Value + "%' ";    
}  else  {
       comm += " AND `NUIP` = `NUIP` ";    
}   
....

WHERE 1 = 1 is optimised away and removes the need to remember if a WHERE is defined yet or not, and all the searches are either optimised away (AND a=a) or have search applied (AND a = xxx).

You just add a single if for each search!

Also I'd use a StringBuilder to clean up the string handling.

edited Jun 11, 2012 at 1:22

answered Jun 11, 2012 at 1:16

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

1 Comment

Erre Efe Over a year ago

+1 for the removal of that where! any idea about the question indeed (the parameterized version of it). Thanks

Collectives™ on Stack Overflow

Parameterized query with dynamic where clasue

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related