It appears that 2 problems are trying to be solved here:
#1 - How to deal with dynamic SQL.
#2 - How to apply the proper protection from SQL injection.
SP's are useful (efficient) for a combination of static SQL and solving #2.
Enter the table-value-function:
Wrap your table SELECTs in TVF's.
Remove all external CRUD permissions from table objects.
Create dynamic, parameterized SQL in your DAL against the TVF's, and SQL Server (2005+) will create optimize planes and reuse cached plans, and give you the best performance (specially when parameterized).
Unless you have defined your SP with RECOMPILE, you will run into a performance wall with large table/multi-join queries because the first "dynamic" SQL plan will be used from cache. (speaking from experience). Please try it.
The key here is solving the "dynamic problem" in the data access layer and not in the the database code.