Alternative to executing dynamic sql

Question

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 Exec.

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 sp_executesql; is this a better solution or are there better conventions for what I am doing?

Update: 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.

example

    var whereClause = new StringBuilder();
    if (Status.Count > 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 ");
    }

Answer 1

Modern RDBMS'es (can't really say whether to consider SQL Server 2000 a "modern" one) are optimized for ad-hoc queries, so there's a negligible performance hit (if any). What's bothering me is that you're using sproc to construct dynamic SQL: this is a huge debugging/support PITA.

Answer 2

You should be using sp_executesql, simply because, as you say, the query plan is stored and future executions will be optimized. It also generally seems to handle dynamic sql better than execute.

Answer 3

sp_executesql is better than exec because of plan reuse, and you can use parameters which help against sql injection. sp_executesql also won't cause procedure cache bloat if used correctly

take a look at these two articles

Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly

Answer 4

Yes, sp_executesql will "cache" the execution plan of the query it executes.

Alternatively, instead of passing part of the query to the stored procedure, building the full query there, and executing dynamic SQL, you could build entire query on .NET side and execute it using ADO.NET command object. All queries executed through ADO.NET are getting "cached" by default.

Answer 5

sp_executesql is the better option. Have you considered not using a stored procedure for this or at least taking out some of the dynamics? I think it would be much safer from any kind of injection. I write filters much like you are talking about but i try to take care of the input in my code as opposed to in a stored procedure. I really like dynamic sql but maybe it's safer to go the extra mile sometimes.