Stored Procedures vs Ad Hoc SQL

Stored procedures debate from my point of view:

Pros of using Stored Procedures

  • Cleaner code.  Thousand lines of logic and sql statements can be traded in for hundreds.
  • Push more work to someone else besides developer.  If you have a dedicated DB guy, this can really help in your timing. 
  • Put much more logic into Stored Procedure then Ad Hoc SQL
  • Faster. Ok, maybe a little bit, but with processing power and RAM does this really matter anymore?
  • Permissions are centralized and encompassed.
  • Trips to the database server can be reduced.

Cons of using Stored Procedures

I’m not entirely sure which side of the fence I lie.  Right now I’m more comfortable with not using stored procedures because of the maintenance factor.  I really think LINQ is going to make stored procedures deprecated.

.NET specific – Here is a nice line of code to keep around when working with stored procedures that I end up using when getting the the dreaded “Parameters do not match” error.  You can put this on your datasource Inserting Event.

For x As Integer = 0 To e.Command.Parameters.Count – 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(e.Command.Parameters(x).Value)

Next

Tim

A .NET, PHP, Marketing Guru authority, at least I hope I am. Reach me at tboland@gmail.com

16 thoughts on “Stored Procedures vs Ad Hoc SQL

  1. Corwin says:

    You can create test harnesses to test/debug the procs. And if you include tons of commmented out tests in the harness, you can easily retest portions as needed. Stored procs should contain an error handling routine that passes back something better than 512.

    Embrace it, logic in the database is a good thing.

  2. Andrew J. Lazarus says:

    Stored procedures are naturally resistant to SQL injection malicious attacks. Rolling your own is not. Of course, if your queries are not dependent on (possibly malicious) user input, this is moot.

  3. That was inspiring,

    interesting post, keep up the good work,

    Thanks for writing, most people don’t bother.

  4. Cambogia 75% says:

    Brilliant masterpiece being written here, made me pretty delighted while reading throughout those and made me enlighten with log ice completely.Cambogia 75%

  5. Awful credit not inquisitive, it may occur because of sum unsettled, defaults in reimbursement, County Court Judgments, chapter 11 and IVAs. Remaining are the days when most exceedingly awful acknowledge was considered as the check for advance. Presently, it is not a thing to be uncomfortable. Anybody can have such sort of trouble and it can wind up simply like a regular chilly.

  6. Marvel Bylsma says:

    Hi threre! Thanks a lot for sharing this amazing article. If you need some help with writing, here you can find best custom essay writing service.

  7. Kiley Muller says:

    Your blog is filled with unique good articles! I was impressed how well you express your thoughts and sharing your experiences.I never regretted any moment that came to your blog! :)
    Value of Market Research Project Help

  8. Suzzane Joanna says:

    Student Essay Help – Student Essay Writing Introduction The main purpose for writing is to render all our knowledge, ideas, and ideas in a written kind

  9. Albert Thomas says:

    Strategic Management writing
    Well thanks for posting such an outstanding idea. I like this blog & I like the topic and thinking of making it right.

  10. James Andy says:

    Term Paper Writing Services
    Thanks for share that type blog. Many people like that blogs….

  11. Eden Alaster says:

    We are Number 1 in Accounting Assignment Help – Accounting Homework Help. Hire us for Accounting Term Papers and Accounting Projects.
    Accounting Help

  12. Hello Tim Boland ,

    I just read your blog thanks for the post but I have some question to you so please let me know can you share with me your email ID then we can discuss more regarding Ad Hoc SQL. I love to share with the knowledge.

    Kind Regards

  13. Helastel says:

    great post, thanks for sharing this Tim.

  14. Hello, i read your blog from time to time and i own a similar one, thanks for sharing this article. This article will be very useful and helpful. Read and understand the article and you will get a brilliant idea. I will share this information with my friends and they will certainly love to read this. Very informative, I’ll definitely give these 5 stars

  15. I Appreciate This Work Amazing Post For Us I Like It.

  16. Science Channel’s Are Giving A Complete Knowledge To Its Viewers About Every Thing Students Write Done Dissertation On This Subjects And Show Its Importance.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>