Alternative to doing dynamic sql

Question

I currently have a Filter object that matches a business object. This object has properties that are associated with various ways that I want to be able to filter / search for a list of such business objects. These filter objects currently have a method that builds the contents of the where clause, which is then passed to the SQL Server 2000 stored procedure, where it concatenates with the rest of the select query. The final line is then executed using Exec .

This works fine now, except that I am worried about a performance issue with lack of execution plan caching. In some studies, I have seen the use of the sp_executesql call; is this the best solution or is there a better deal for what i am doing?

Update: I think part of the problem with using sp_executesql is that based on the collection in my filter, I need to generate a list of OR statements. I am not sure that a "parameterized" query will 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

sp_executesql is better than exec because of plan reuse, and you can use parameters that help against SQL injection. sp_executesql will also not cause the cache cache to swell if used correctly

take a look at these two articles

Avoid Conversion in Execution Plans with sp_executesql Instead of Exec

Changing exec to sp_executesql doesn't do any good if you use parameters incorrectly

Answer 2

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

Alternatively, instead of transferring part of the request to the stored procedure, building the full query and executing dynamic SQL, you can build the entire query on the .NET side and execute it using the ADO.NET command object. All requests made through ADO.NET become "cached" by default.

Answer 3

You should use sp_executesql, simply because, as you say, the query plan is stored and future executions will be optimized. It also typically handles dynamic sql better than execution.

Answer 4

Modern RDBMSs (I can’t say whether SQL Server 2000 should be considered “modern”) are optimized for ad-hoc queries, so there is a slight performance hit (if any). My concern is that you use sproc to build dynamic SQL: this is a huge PITA debugging / support.

Answer 5

sp_executesql is the best option. Did you think you weren't using a stored procedure for this, or at least take out some kind of dynamics? I think it would be much safer from any injection. I write filters the way you say, but I try to take care of entering my code, not the stored procedure. I really like dynamic sql, but it may be safer to do extra mil sometimes.