SYSK 41: “The Curse and Blessings of Dynamic SQL” by Erland Sommarskog

  • Article

I came across this article a couple of days ago.  You can see the full text at http://www.sommarskog.se/dynamic_sql.html, but the summary is below.  Enjoy!

Dynamic SQL is a very powerful feature that allows you to construct and execute T-SQL statement at run time by calling sp_executesql or EXEC commands.

Stored Procedure Pros:
      1. Controlled access to data.  Dynamic SQL requires you to give users direct access to the tables; and that is prone to SQL injections.
      2. Improved performance due to
            a. cached query plan
            b. minimized network traffic
            c. Ability to use output parameters, rather than a resultset
      3. More reusable and maintainable
      4. Easier to find dependencies.  E.g. you can use sp_depends to find out where a certain table or column is reference.

Dynamic SQL (exec() and sp_executesql) Cons:
      1. Access stored procedure’s variables/parameters can only be done by passing them as input/output params to the dynamic SQL (similar to stored proc), and is only available in sp_executesql
      2. SET, USE commands, temp tables, cursors, etc. created in dynamic SQL are not available to the stored procedure that invoked it (dynamic SQL has its own scope), and vice versa
      3. Termination (exception) of the batch, terminates the entire SQL calling chain
      4. Can’t use sysdepends to find referenced objects
      5. Susceptible to SQL-injections (a technique whereby an attacker can cause your SQL code to execute something completely different from what you intended)

Summary: favor stored procs over dynamic SQL. When you think you must use dynamic SQL, stop and double check yourself – you might’ve missed a way to do it (e.g. a CASE statement, better database design)…

Acceptable exceptions of using dynamic SQL include:
• Get data from another database. If you for some reason have your application spread over two databases, you should not litter your code with hard-coded references with database names, because the day want you a test environment on the same server, you will have a problem.
• For sysadmin tasks
• Calling a stored proc on a linked server