none
Dynamic sql usage? RRS feed

  • 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

    Wednesday, March 4, 2015 6:17 AM