CODEWITHSUNDEEP
.netsqlsql-server
teenup
teenup

Reputation: 7667

What's the point of writing a dynamic query in a stored procedure

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.

Upvotes: 3

Views: 166

Answers (2)

KM.
KM.

Reputation: 103667

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.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175916

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)

Upvotes: 2

Related Questions