Tuesday, August 23, 2011

Dynamic SQL vs. Stored Procedures

     So much has been written about the pros and cons of Dynamic SQL vs. Stored Procedures, with a lot of venom coming from both sides. As a DBA, I really push hard for the use of Stored Procedures as the only way to access or manipulate data, but as an ex-developer, I see the allure of Dynamic SQL.

     The purpose of this post is not add to yet another article to the "flame ridden" posts already out there, but to get a sense of where people stand and what people are using in their shops. Please reply to this post with which side you come down on, your job title (if applicable), and either an existing article or brief summary that best describes why you feel the way you do.

2 comments:

  1. Currently a Senior Consultant, previously a Senior Data Architect. Stored procedures 100%. Primarily because if we have 20 apps that consume from the database, and they use 5 different data abstraction layers (and some bypass the DALs), changing the behavior of a stored procedure is a one-place change, rather than changing all the apps, re-deploying, etc. You're going to pay for the testing regardless, but I'd rather change one line of code in one place than one line of code in 25 places.

    ReplyDelete
  2. Sorry, forgot to post a link.

    http://stackoverflow.com/questions/1627442/better-to-use-stored-procedures-or-sql-in-my-code-for-working-with-data/

    Picked one without a lot of flaming, venom etc. Anyway my arguments there (and others that I avoided for brevity) have stood up for my 14 years in the industry at different companies.

    ReplyDelete