Advertisements
Home > SQL Server Questions > Dynamic SQL usage, when to use? – MSDN TSQL forum

Dynamic SQL usage, when to use? – MSDN TSQL forum


–> 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.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: