Alternative To Executing Dynamic Sql

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 l

Solution 1:

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.

Solution 2:

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

Solution 3:

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.

Solution 4:

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.

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