Consider the following guidelines when choosing between stored procedures and dynamic SQL:

• If you have a small application that has a single client and few business rules, dynamic SQL is
often the best choice.
• If you have a larger application that has multiple clients, consider how you can achieve the
required abstraction. Decide where that abstraction should exist: at the database in the
form of stored procedures, or in the data layer of your application in the form of data access
patterns or O/RM products.
• If you want to minimize code changes when the database schema changes, consider using
stored procedures to provide an abstraction layer. Changes associated with normalization
or schema optimization will often have no affect on application code. If a schema change
does affect inputs and outputs in a procedure, application code is affected; however, the
changes are limited to clients of the stored procedure.
• Consider the resources you have for development of the application. If you do not have
resources that are intimately familiar with database programming, consider tools or
patterns that are more familiar to your development staff.
• Consider debugging support. Dynamic SQL is easier for application developers to debug.
• When considering dynamic SQL, you must understand the impact that changes to database
schemas will have on your application. As a result, you should implement an abstraction in
the data access layer to decouple business components from the generation of database
queries. Several patterns, such as Query Object and Repository, can be used to provide this
abstraction.
Application Architecture Guide 2.0a
Microsoft

martin flower

http://www.martinfowler.com/articles/dblogic.html