Stored Procedures

• Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records)
• Pro: Keeps database logic separate from business logic
• Pro: Easy to troubleshoot
• Pro: Easy to maintain
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Compiled in database
• Pro: Better security (users don t need direct table access)
• Pro: Excellent query plan caching (good for OLTP queries--benefits from plan reuse)
• Con: Excellent query plan caching (bad for OLAP queries--benefits from unique plans)
• Con: Makes you tied to that SQL vendor

Dynamic SQL (i.e. uses exec command within a stored procedure)

• Pro: Good for short, simple queries (aka OLTP)
• Pro: Keeps database logic separate from business logic
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Allows any table, database, or column to be referenced
• Pro: Allows predicates (in WHERE clause) to be added/removed based on parameters
• Pro: Good query plan caching (mediocre-to-good for both OLTP and OLAP queries)
• Con: Only the static elements of the proc can be compiled
• Con: Makes you tied to that SQL vendor
• Con: More difficult to troubleshoot
• Con: More vulnerable to SQL injection attacks

Ad Hoc SQL (i.e. created in your business code)

• Pro: Good for long, complex quieres (aka OLAP--i.e. reporting or analysis)
• Pro: Flexible data access
• Pro: ORM usage is possible; can be compiled/tested in code (i.e. Linq-to-Sql or SqlAlchemy)
• Pro: Poor query plan caching (good for OLAP queries--benefits from unique plans)
• Con: Poor query plan caching (bad for OLTP queries--benefits from plan reuse)
• Con: More bits transferred over network (i.e. the whole query and params)
• Con: More difficult to maintain, if you don t use an ORM
• Con: More difficult to troubleshoot, if you don t use an ORM
• Con: More vulnerable to SQL injection attacks

Note: Always parameterize your ad hoc SQL.

For OLAP ad hoc SQL: only parameterize string data. This satisfies two conditions. It prevents SQL injection attack. And it makes the queries look more unique to the database. Yes, you ll get a poor query plan cache hit ratio. But that s desirable for OLAP queries. They benefit from unique plan generation, since their datasets and most efficient plans vary greatly among given parameters.

Stored procedures PROs:

• Compiled. This means that it s faster to run and has positive impact on your database server s CPU due to bypassing optimization/compilation stage for all but first execution.
• Allow clean permissioning control over complex read and write queries.
• Provide for reusable API allowing one GOOD efficient implementation, instead of a bunch of Yahoos on a variety of platforms from a variety of apps re-implementing the samke queries and risking getting inefficient implementations
• Like any API, provide abstraction layer. You can change underlying implementation (schema) without changing any code calling the SP. That s an extremely big plus when there s 100s of apps across all platforms which use the query.

Stored procedures CONs:

• Hard to code flexible logic compared with dynamic SQL
• Having pre-compiled version can lead to less efficient execution as your data drifts and optimizer choices change. This is easy to ameliorate by re-compiling once in a while.

Stored procedures

• Pro: Permissioning of actions without needing to grant more fundamental rights at the table level.
• Pro: Discrete and versionable
• Pro: Allows you to isolate your schema from your data access code.
• Con: Can be tedious to code CRUD procedures
• Con: Need to be kept in line with underlying schema

Ad hoc and dynamic - see Bill Paetzke s answers and comments.

Also, don t forget patterns such as bulk insert for SQL which isn t in your list but should still be considered.

RDBMS? This answer is specific to older oracle

In older oracle version < 11, dynamic sql does not reuse existing SGA sqltext plans, it creates a new entry for every execution plan the parser needs. With a lot a dynamic sql calls the sqltext area gets flushed fast enough that query reuse goes way down and peformance follows it on down.

One additional advantage is easier "No downtime upgrades" (for major upgrades you still might incure some downtime).

if all data access is done through stored procedures, you can easily have v1 and v2 of the stored procedures sit side by side.

now you can have binaries/application logic from v1 and v2 running side by side, each calling their own version of stored procedures.

no down time is achieved through 1, locking down v1 app into readonly mode (if applicable), 2, deploying db changes. 3, reenabling normal access to v1 app, 4, deploying v2 app side by side, tell new users to use new binaries. 6. shut down old binaries when no more users are using the old binaries.

IMHO stored procedures should be avoided like the plague. Here s ten good reasons off the top of my head for why you should never use them (applies to all databases):

1. The PL/SQL language is geared towards handling data in tables, rows and columns. It is a poor choice for expressing business logic. You can code anything in any language - that doesn t mean you should
2. Most databases lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
3. Human resources are harder to find to write and maintain stored procedures - they are simply much rarer and therefore more expensive
4. Stored procedures are not portable between databases, because a) there is no industry standard for PL/SQL b) even if there was a standard, you usually end up using database-specific functionality/sql inside your stored procs. If you ever have to move dbs, you re looking at a full re-write of your business logic
5. Most databases don t offer any support for stored procedure debugging - you re left to insert rows into a log table or similar to achieve logging for debugging - very ugly
6. To test the stored procedure you need a real database instance. This makes unit testing stored procs difficult (you have to deploy them to a dev db to run them)
7. To deploy stored procs, you have to update the database (drop then create the stored procedure). If a bug is discovered, you can not simply roll back to a previous binary release like you can with app code. Instead, you must find the old code, drop the new stored proc and (re)create the old one. This is a change on top of a change, not a roll back
8. You are increasing the database server s processing demands, rather than distributing the business logic to other (app) servers. Since the database is usually a singleton, this is very bad, because the only way to increase capacity is to buy better hardware (not buy more hardware or use the cloud)
9. They are not that much faster than well written queries using prepared statements, because there s a trade off between increasing processing demand on the database server and the efficiencies of using them. Besides speed isn t everything (as long as it s acceptable): Maintainability, debuggability, suitability of PL/SQL, etc are just as important if not more so
10. Stored proc languages have limited (if any) libraries to draw upon, so you end up writing lots of low-value code. This is unlike app languages, which have loads of libraries you can use to all the things that business logic needs

There is only one place I would sanction their use: For very specific database functionality - perhaps a key check or a data type conversion or something similar, maybe within a trigger, that is so important it justifies its existence and that probably won t ever change once written.

In general, you should run screaming from stored procedures!