A stored procedure is a procedure
that is stored on the database server.
Usually written
in SQL, the stored procedure benefits from the power and proximity
of the database from which it is managed.
Stored procedures offer other benefits.
- They encapsulate code. In other words, the database operation appears once in the stored
procedure, not multiple times throughout your application source. Both debugging and maintainability
are improved as a result.
- Changes to the database schema affect your source code in only one place; the stored procedure.
Any schema changes then become a task for a database administrator, rather than a complete code
revision.
- Because the stored procedures are on the server, you can set tighter
security restrictions on the client space, saving more trusted database
permissions for the well-protected stored procedures themselves.
- Stored procedures are compiled and stored outside the application. Therefore, they can use more
sensitive variables within the SQL syntax, such as passwords or personal data that you would not use
in scripts or remote calls.
- Using stored procedures greatly reduces network traffic.
With a Mapping node, you might have multiple database SELECT transforms. Each SELECT transform
is executed by sending a message over the network to the server. Often the server sends a response
in return. But a stored procedure resides on the server. When the client application calls the
stored procedure, the stored procedure runs on the server. The stored procedure responds only when
it returns the final result set to the client, saving network traffic.