Context for the database stored procedures scenario

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.