>Hi, Mike.
>
>>I was told a stored procedure is ALWAYS faster than dynamic SQL. However I've read that this is a myth.
>>
>>I know there has to be reasons where the SP will beat the dynamic SQL - as in cases where the SP has to do major processing where it's proximity to the data will help.
>>
>>However this seems to indicate a report writer that generates a dynamic SQL should beat a simple SP.
>
>As Cetin said, it is a myth that SP are ALWAYS faster. Indeed, in most modern engines the optimizer is smart enough that dynamic SQL could be very efficient most of the times, minimizing the effect of the SP precompilation. That been said, for complex query structures with several optional filters, dynamic SQL can b a better solution. A generic SP should have a lot of ugly code to handle some situations, whereas dynamic SQL can have just what it needs to solve the given filters and joins.

Yes. By the way, there is not precompilation of SPs.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

>
>SPs are sometimes perceived also as more secure than dynamic SQL. This is not always true, either. Rend-only access for querys over specific tables or views can be as secure, and more flexible. Unfortunately, many DBAs are still sticking to a SP-only mindset that make us developers having to resort to all kind of convoluted logic.
>
>As usual, there is a place and time for each of the alternatives.