Dynamic sql usage?

Question

Hello Everyone,

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

Regards

---

Regards

Answer 1

An appropriate use case for dynamic SQL is dynamic search conditions that make static SQL unwieldy.    See http://www.sommarskog.se/dyn-search.html for parameterized examples using sp_executesql. 

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

When your SQL query is not static and Column name or Table or WHERE clause condition parameters have to passed dynamically, then use dynamic SQL.

E.g

DECLARE @sql varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
------------------
EXEC (@sql)
------------------

And if you care about SQL Injection, then use sp_executesql

---

-Vaibhav Chaudhari

Answer 3

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.

Check this blog post for EXECUTE vs sp_executesql.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page