|I'm glad I'm not the only one here who dislikes stored procedures (or at least the blind overuse of them).
Let me preface:
Before .net I did most of my database application programming with SQL embedded in C, either with Oracle (PRO*C) or RDB.
But at one point I had to work on a project that used SQL Server (6, as I recall) and ESQL was not yet available so we had to use a horrible (consultant-written) library of ODBC routines. The result was that the only way to have transactions was to use stored procedures.
No one was happy about it and thought very nasty thoughts about the sales monkeys who said we'd do it.
While I realize that newer versions of SQL Server offer better security and reliability, I continue to distrust stored procedures:
A) While installing some of my stuff at a client's site, the client asked if I could make a slight change; I did, it was just a small change to a stored procedure... a change to production code outside of source control. I did remember to check in the updated code when I returned to the office, but I realized that any (properly authorized) person could change any of the procedures, perhaps maliciously. Such changes are much more difficult with compiled code.
B) Several times a stored procedure just disappeared! I have no idea how. This either doesn't happen with compiled code, or at least the program won't run at all if it does.
A) I can neither confirm nor deny whether or not procedures are faster than queries in strings. I suspect they are, though at best it's only due to preparation time.
Unless you have a procedure that gets called a whole lot (which may well be the case with a Web page), I doubt it matters.
If a procedure gets called less frequently then you're not benefiting from any performance improvement.
If the preparing of a particular SQL statement proves to be a bottleneck, by all means wrap it in a stored procedure.
B) But also remember that a parameterized ADO.net SqlCommand, once prepared, can be reused many times.
So if you have a Windows Service that needs to execute a particular statement many times during its execution (which should last months) it need only prepare the statement once, just like a stored procedure.
On the other hand, if you repeatedly create and dispose an SqlCommand that executes a stored procedure lose out on much of the benefit of having pre-compiled the code in the stored procedure.
C) One of the benefits of a stored procedure is that you can perform some logic on the data without passing it out of and back into the database, but I wonder what percentage of stored procedure actually involve that.
D) Add to that the recent post that pointed out that if you begin the names of your stored procedures with "sp_", SQL Server wastes time looking for it among the system procedures.
E) I have also worked with people who insisted that "metadata" (stored procedured, functions, etc.) bogged down the database. (Crazy as that seems.)
A) If I have an application installed at several client sites I can check the installed versions with directory commands.
B) I can deploy a change to client systems with copy.
C) A database restore won't wipe out SQL code in compiled code.
D) I have no idea why someone would write a stored procedure that merely returns a table of data; use a view for that.
I also use quite a few inline table-valued functions; they combine the best of both views and stored procedures.
Don't just use stored procedures unthinkingly:
If the statement is a proven bottleneck, first try improving the statement, only make it a stored procedure as a last resort.
A pre-compiled piece of crap is little better than an ad hoc piece of crap. (And it may be more visible to the client.)
Conceptually, a "procedure" has more than one step, and perhaps control statements; if your code is one simple statement I doubt it should be a stored procedure.
"Use the right tool for the right job." -- Scotty et al
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.