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
- Much harder to debug. No UI debugger, stepping thru the code.
- Harder to tell were errors lie. Does this lie at the stored procedure or my code calling it? Plus the error codes returned by SQL are cryptic - “Error 512 was called” - Oh yeah, good old error 512.
- Parameter list can be really long, which takes away from the cleaner code.
- “Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves.” This is the main point that causes me to stay away from 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
Written by Tim on June 1st, 2007 with
1 comment.
Read more articles on asp.net and database.
- [+] Digg: Feature this article
- [+] Del.icio.us: Bookmark this article
- [+] Furl: Bookmark this article
#1. July 26th, 2007, at 7:06 PM.
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.