Is there an alternative to dynamic stored procedures?

I hate 开发者_Python百科buikding these for a variety of reasons; debugging, lack of execution plan, permissions issues, etc. Is there a viable alternative to building huge dynamic sql statements in a stored proc and then executing it?

I sometimes avoid dynamic SQL by deleting rows from a temp table that I don't want. But I'm not saying that this is best for performance - I work in a data warehouse more often than not where performance is second to other factors, like avoiding dynamic SQL.

Here is an example, for a report that calls for TOP x records, I choose the max, say 200, and simply delete the rest. (I.e., 200 is the maximum the report will ever need. I hope.) So there will be, if the parameter's name is @Range:

SELECT TOP 200 
...

DELETE FROM #MyTemp where Counter > @Range