Microsoft® SQL Server® 2008 Internals

When to Use Stored Procedures and Other Caching Mechanisms

Keep the following guidelines in mind when you are deciding whether to use stored procedures or one of the other query mechanisms:

• Stored procedures. These objects should be used when multiple connections are executing batches in which the parameters are known. They are also useful when you need to have control over when a block of code is to be recompiled.

• Adhoc caching. This option is beneficial only in limited scenarios. It is not dependable enough for you to design an application expecting this behavior to correctly control reuse of appropriate plans.

• Simple or forced parameterization. This option can be useful for applications that cannot be easily modified. However, it is preferable ...