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 wholesale 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.
Consequently, 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 it, it runs on the server. It responds only when it returns
the final result set to the client, saving network traffic.