Two main ways to interact with the database: using a stored procedure vs.
using a direct SQL statement. Here's a brainstorm of the pros and cons to each:
| Technique (called from managed code): |
Pro |
Con |
| Stored Procedure |
- Performance, as SP is already on the database
- Reusability - multiple sources (including other SP) can call a SP.
- Security, especially against SQL injection attacks.
- Maintainability - you can create the SP (and run and test it) in a
database editor.
- Powerful - A SP consists of multiple SQL statements.
- Deployment - Modifying a database object is often easier than
redeploying managed code.
- Code Generation - You can code generate a data access layer off of
stored procs (although most of the time it's code-generated off of table
schemas).
|
- Have to install each SP on the database
|
| Sql Statement |
- Quicker to get started, especially for simple code and a RAD,
non-production app.
- Sql string is stored in managed code, so no need to install on the
database.
- Flexible - you're generating any SQL statement you want.
|
- Lacks most of the benefits of a SP.
|
In general (of what I've seen), many enterprise apps will use Stored Procs,
while sometimes a RAD will use raw SQL statements.
What has your experience been?
tags: database
links: digg this technorati
I'll add to both of your lists.
Pro - SP's can be referred to simply by name and parameters.
Pro - SP's are backed up and moved with the database itself
Neutral - SP's are like an RPC layer for your database which can be good
and bad
Con - SP's are a pain to manage for change control purposes (SAS-70, sarbox
etc)
Con - SP's can execute multiple statements. Disregarding any psuedo
religion about OO vs data driven design, putting cpu cycles in the database
taxes your most precious resource.
Pro Sql statements are easy to version
Correction - Sql statements can use parameters just like SP's to avoid
injection attacks
Neutral - Sql statement execution plans are cached just like SP's these
days so performance is very similar
