Frank,

I've read your article and I am a bit confused.

I accept the performance overhead of dynamic SQL (not caching query plans) and the security aspects (giving specific permissions on tables rather than just exec permissions on procedures).

However, I'm not sure whether in this case you are making us aware of the pitfalls of dynamic SQL or suggesting an alternative way?

I use a lot of dynamic SQL in my app and I'm not sure now whether it is the best solution. My users run a about 12 different sets of data each month which summarise data from a database containing around 150 million rows. These summaries can range in size from a tens of thousands of rows up to 100 million rows. So that I can keep the app running whilst creating the data, I have a separate table for each run - this also makes it easy to delete old data (drop table rather using a delete statement).

Where I use dynamic SQL is to select the different tables:

set @sqlstring = N'select col1, col2, col3

from [' + covnert(varchar,@runid) + '_table

where ....

I have had to give users select permission only on the tables (its a reporting app so no need for inserts or updates) as they need it.

I welcome you views on whether I am using dynamic SQL wisely or foolishly.

Jeremy