Pages

Saturday, 24 August 2013

When and why you should use 1=1 in WHERE clause?

One Interviewer ask this question during interview of my friend.It is easy to answer but need some specific and to the point answer.So I try to relate it with C#.
 If you don't know  the list of conditions at compile time and it will built at run time, Then you can made a condition with “where 1=1”. and for other conditions that will affect run time, use

and  <condition>.
Example

StringBuilder sb = new StringBuilder();
         sb.Append("SELECT * FROM Products");  // Your query
         sb.Append(" WHERE 1=1"); // always true condition
 
         // append query's where clause
 
         if (catID != 0)
         {
             sb.Append(" AND categoryID= {0}", catID);
         }
         if (minPrice > 0)
         {
             sb.Append(" AND itemPrice >= {0}", minPrice);
         }
 
         SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
         SqlDataReader dr = cmd.ExecuteReader();
         // your code to read data from dr.



.

No comments:

Post a Comment

Please leave a comment for this post