Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Stored Procedure Advantages

Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including

• Execution plan retention and reuse

• Query autoparameterization

• Encapsulation of business rules and policies

• Application modularization

• Sharing of application logic between applications

• Access to database objects that is both secure and uniform

• Consistent, safe data modification

• Network bandwidth conservation

• Support for automatic execution at system start-up

I'll touch on each of these as we go along.