Thursday, October 26, 2006

Why use Stored Procedures over direct SQL calls?

[This was originally posted at]

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?

No comments:

Post a Comment