Answered by:
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
Wednesday, March 4, 2015 3:18 AM
Answers
-
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
- Marked as answer by Sqlfav Wednesday, March 4, 2015 8:12 AM
Wednesday, March 4, 2015 5:06 AM -
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
- Proposed as answer by Visakh16MVP Wednesday, March 4, 2015 5:42 AM
- Marked as answer by Sqlfav Wednesday, March 4, 2015 8:12 AM
Wednesday, March 4, 2015 3:34 AM
All replies
-
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
- Proposed as answer by Visakh16MVP Wednesday, March 4, 2015 5:42 AM
- Marked as answer by Sqlfav Wednesday, March 4, 2015 8:12 AM
Wednesday, March 4, 2015 3:34 AM -
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
- Marked as answer by Sqlfav Wednesday, March 4, 2015 8:12 AM
Wednesday, March 4, 2015 5:06 AM -
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- Proposed as answer by Manoj Pandey (manub22)Microsoft employee Friday, October 23, 2015 11:25 AM
Wednesday, March 4, 2015 6:17 AM