If we need some information that can be achieved only through dynamic query then should not it be written in the application code directly (in DAL) rather than in the stored procedure?
Because the benefit we achieve from SP is already lost if we use dynamic query in it which is the already saved execution execution plan along with it.
There are many other facets of stored procedures to consider besides their execution plan caching features, so I don't think its fair to dismiss their use simply because they are going to contain an ad-hoc query.
(Also worth noting that a properly formed bit of dynamic SQL is no barrier to execution plan reuse)
if your application currently only calls stored procedures, then keep it consistent and call a stored procedure and do the dynamic SQL in there.
if you application is already full of generated SQL statements just build the dynamic SQL there.
Remember that using a stored procedure to dynamically generate SQL will not protect you from an injection attack. However, the procedure's parameter type and size restrictions can help reduce your injection exposure.