Marc Gravell said:
A bit OT, really, but...
I'm an SP fan myself, but to counter the existing posts:
I liked your post Marc, and here are my 2 cents.
* in many cases, on "decent" RDBMS, the performance will be very
comparable; re-use of query plans is better than it used to be,
especially with parameterised commands. SPs might be faster, but often
not by as much as you'd think [see also last point]
This is why I don't decide to you use or the other technique, but always
start a new project with the idiom of 'we will use both stored procedures
and dynamic sql'.
This way, a huge chuck of sql can always be moved into a sproc or vice
versa.
* ideally, and command you write would be parameterised; as such, it
doesn't give you any additional injection protection unless you are
writing your non-SP commands badly
This is a valid point. While you may set up rules that all coders MUST use
parameters, when things get stressful, coders tend to 'forget' that rule,
starts to code sql by concating strings; and open up for sql-injection.
I think this is the argument that the hardcore SP-fanatics use. If you give
the coders full access to the database, they MIGHT do stupid/unsecure things
with it.
Additional points:
* SPs tend to be vendor specific (perhaps not even existing on your
RDBMS); if vendor portability is an issue, then ORM like NHibernate or
(later) D-LINQ might be of interest; this is almost always raw
(parameterised) commands
I agree. Linq for SQL is a neat way of handling CRUD for instance.
CRUD via SQL is just tiresome.
And I don't know how many times I have written a DELETE statement for Remove
and forgot to add the (very important *s*) WHERE clause. Ouch =)
* SPs allow you finer security control; it is hard to hack a database
if you don't have CREATE/SELECT/INSERT/UPDATE/DELETE permissions, but
can only EXEC existing (approved) SPs
This is true.
But at the same time, having the typical 5 sprocs for Create, Update, List,
Fetch, FetchAll per table gives you an enourmous amount of sprocs. Just
navigating and managing all the sprocs is a nightmare.
* You can change the SPs without recompiling the client; especially
useful if clients with different architectures (java, .Net, gupta, etc
- perhaps a few reporting tools too) call the same SPs
I have heard this argument before. But i think it is a weak argument
How often do you change the table-schema without making changes in the
client(s)?
* SPs can act kinda like a contract, creating a reasonable join-point
between technologies; unfortunately, the grids/columns aren't normally
part of that contract (just the name and params) so this is a weak
metaphor
Hmm. One could argue that classes also serves as the contract.
If a bug is in the SP or in dynamic sql, it is still the same; a bug.
* Sometimes, freaky performance just happens. I've seen massive
performance changes just be moving an obscure, inoffensive [i.e. runs
instantly on its own] line from an SP to a second SP and calling EXEC.
And yes, I understand lots about recompiles (DDL/DML interleave, KEEP
PLAN, etc) - it wasn't that simple. Hard to do that using raw SQL
without messy hacks like sp_ExecuteSQL
* Sometimes, the SQL to do something that looks simple is just
offensively complex; my current example here is some "temporal
database" work I have been doing. In which case, you can script your
SPs (however you want) and invoke a 1-line command instead of sending
a huge command down; this is the main counter-point to my first point:
sometimes, size does matter.
This is why I like to be able to use both sprocs and dynamic sql.
if I notice that what was at first a benign sql-statement, turning into a
blob, I can always move it into a sproc.
I remember a funny wtf at worsethanfailure.com. A company had a policy of
using sprocs only. What do you do?
ALTER PROCEDURE DoSQL(@sql varchar(8000))
AS
EXEC (@sql)
RETURN
QED - Quite Easily Done =)
- Michael S
