Advantages of Stored Procedures

Using stored procedures provides many advantages over executing large and complex SQL batches from client applications:

• Modular programming?Subroutines and functions are often used in ordinary 3GL and 4GL languages (such as C, C++, and Microsoft Visual Basic) to break up code into smaller, more manageable pieces. The same advantages are achieved when using stored procedures, with the difference that the stored procedure is stored in SQL Server and can be called by any client application.

• Restricted, function-based access to tables?Someone can have access to execute a stored procedure without having permissions to operate directly on the underlying tables.

• Reduced network traffic?Stored procedures can consist of many individual SQL statements but can be executed with a single statement. This allows you to reduce the number and size of calls from the client to the server.

• Faster execution?Stored procedures query plans are kept in memory after the first execution. The code doesn't have to be reparsed and reoptimized on subsequent executions.

• Enforced consistency?If users modify data only through stored procedures, problems resulting from ad hoc modifications are eliminated.

• Reduced operator and programmer errors?Because less information is being passed, complex tasks can be executed more easily with less likelihood of SQL errors.

• Automated complex or sensitive transactions?If all modifications of certain tables take place in stored procedures, you can guarantee integrity on those tables.

Some of the disadvantages of using stored procedures follow (depending on environment):

• Less powerful programming language?T-SQL is not the most powerful or structured programming language.

• Less integration with programming environment?Many of the larger software development projects use tools for version handling, debugging, reuse, and so on. Those tools might not support code stored within stored procedures.

• Less portability?Although the ANSI-99 SQL standard provides a standard for stored procedures in database management systems, the format and structure is different from SQL Server?stored procedures. Also, few DBMS vendors currently support the new standard.

The question is, "Should you use stored procedures?" The answer is (as it often is), "It depends."

If you are working in a two-tier environment, stored procedures are often advantageous. The trend is shifting to three- (or more) tier environments. In this case, business logic is often handled in some middle tier (possibly ActiveX objects managed by Microsoft Transaction Server). If that is your environment, you might want to restrict the stored procedures to performing basic data-related tasks, such as retrievals, inserts, updates, and deletions.