Microsoft® SQL Server™ 2000 DBA Survival Guide, Second Edition

Stored Procedure Pros and Cons

Before further defining a stored procedure, here are some of the pros and cons of using stored procedures:

Stored Procedure Pros:

• Stored procedures enable you to perform complex operations that cannot be performed with straight SQL.

• Stored procedures offer a substantial performance gain over standard SQL statements because the SQL statements in a stored procedure are precompiled. An execution plan is prepared after the stored procedure executes the first time. After the execution plan is created and stored in the procedure cache, subsequent execution of the stored procedure is much faster than equivalent SQL statements.

• Stored procedures can be used as a security mechanism. For example, if you have many tables or ...