I have found dynamic SQL statements to be faster in many situations where the exact nature (and number, when using optional parameters) of passed parameters is unknown at the time a static stored procedure is saved.

In these cases the query is often not optimised to the execution plan that is the best plan for the parameters employed and their values and a dynamic query that forces parsing and optimisation will run 2x to 3x faster.

Additional Tips for enhanced readability of code:

Use plenty of whitespace, it makes no discernable difference in execution time;

Build string literals into your dynamic queries using double quotes and then use the Replace() function to fold them to single quotes before execution;

[font="Courier New"]SET @SQL =

'

SELECT LastName + ", " + FirstName

FROM MyTable

WHERE LastName LIKE "knigh%"

'

SET @SQL = Replace(@SQL, '"', '''')

EXEC(@SQL) [/font]

I use these methods in an 8000+ user classic ASP app, with SQL 2000 databases having 6 to 10 million records in the main transaction and master balance tables--with very acceptable performance.