SQL with Manoj

–> Question:

Can someone tell when or where to use Dynamic SQL ( exec sp_executesql and exec commands) over normal SQL?
 

–> My Answer:

Dynamic SQL queries should be avoided and one should put more thought and time on creating non-dynamic SQL statements.

But there could be some rare scenarios or requirements where you need to create Dynamic SQL, like doing some multiple DBA activities in one go, like:

– Enabling/Disabling multiple Jobs at once, link.

– Creating Dynamic PIVOT where there could be multiple columns based on multiple rows, link.

– Use sp_executeSQL instead of EXEC (SQL statement) to avoid SQL-Injection.

– Check this article by Erland on Dynamic SQL.

– Check this blog post for EXEC (SQL statement) vs sp_executeSQL.
 

Ref Link.