Stored procedures encapsulate SQL logic from "summary" of SQL and Relational Theory by C. Date
Stored procedures are an essential feature of SQL implementations. The basic idea is to store SQL code on the server, instead of sending it over the network from client to server each time it needs to be executed. This approach has several advantages. For example, it can improve performance by reducing network traffic. But an even more important point is that stored procedures encapsulate SQL logic. That is, they allow you to encapsulate a sequence of SQL operations behind a single name. This encapsulation has several important benefits. For one thing, it can simplify the client's job. Instead of having to issue a whole series of separate SQL statements, the client can simply call the relevant stored procedure. That makes life easier for the client, and also reduces network traffic. Moreover, it can improve security. By defining an appropriate interface to the database, you can control exactly what operations are allowed, and by whom, thus helping to protect the integrity of the database. Another advantage is that encapsulating SQL logic in a stored procedure can improve maintainability. Suppose, for example, that the logic involved in some particular database operation needs to be changed. If that logic is encapsulated in a single stored procedure, you need to make that change in just one place, not in multiple places. Moreover, by using stored procedures, you can take advantage of the database system's facilities for enforcing integrity constraints. This is important, because these constraints are a key part of the logical database design. By encapsulating the operations that enforce these constraints within stored procedures, you can ensure that they are always enforced, regardless of how the database is accessed.- Then, stored procedures are an important tool in the database developer's kit. They can simplify the client's job, improve security, enhance maintainability, and help enforce integrity constraints. For all these reasons, you should consider using stored procedures whenever appropriate in your database applications.
