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 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.
- Autoparameterization This option can be useful for applications that cannot be easily modified. However, it is preferable when you initially design your applications that you use methods that explicitly allow you to declare what your parameters are and what are their datatypes, such as the two suggestions below.
- The sp_executesql procedure This procedure can be useful when the same batch might be used multiple times and when the parameters are known.
- The prepare and execute method These methods are useful when multiple users are executing batches in which the parameters are known, or when a single user will definitely use the same batch multiple times.
© Microsoft. All Rights Reserved.