What is the point of writing a dynamic query in a stored procedure

Question

If we need some information that can be achieved only through a dynamic query, should it not be written directly in the application code (in DAL), and not in a stored procedure?

Since the advantage that we achieve from SP is already lost if we use a dynamic query in it, which is an already saved execution plan with it.

Answer 1

There are many other aspects of stored procedures to consider besides the caching functions of the execution plan, so I don't think its fair rejection of their use is simply because they will contain an ad-hoc request.

(It’s also worth noting that a properly formed dynamic SQL bit has no barrier to reusing the execution plan)

Answer 2

if your application currently only calls stored procedures, then keep it consistent and call the stored procedure and do dynamic SQL there.

If the application is already populated with generated SQL statements, just create dynamic SQL there.

, SQL . , .