title: Alternative to executing dynamic sql
body: <p>I currently have a 'Filter' object which corresponds to a business object.  This object has properties that relate to the different ways that I want to be able to filter/search a list of such business objects.  Currently these Filter objects have a method that builds the contents of a where-clause that is then passed to a SQL Server 2000 stored procedure where it is concatendated with the rest of the select query.  The final string is then executed using <strong>Exec</strong>.</p>
  
  <p>Currently this works fine except I worry about the performance issue with the lack of execution plan caching.  In some research I have seen the use of calling <strong>sp_executesql</strong>; is this a better solution or are there better conventions for what I am doing?</p>
  
  <p><strong>Update:</strong> I think part of the issue with using sp_executesql is that based on a collection in my filter I need to generate a list of OR statements.  I am not sure that the 'parameterized' query would be my solution.</p>
  
  <p><em>example</em></p>
  
  <pre><code>    var whereClause = new StringBuilder();
      if (Status.Count &gt; 0)
      {
         whereClause.Append("(");
         foreach (OrderStatus item in Status)
         {
            whereClause.AppendFormat("Orders.Status = {0} OR ", (int)item);
         }          
  
         whereClause.Remove(whereClause.Length - 4, 3);
         whereClause.Append(") AND ");
      }
  </code></pre>