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

19 thoughts on “Stored Procedures vs Ad Hoc SQL

  1. 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. 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. 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.

  4. 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

  5. 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

Leave a Reply

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