Benefits of Stored Procedures

There are multiple advantages of using stored procedures in your application. The key benefits of this technology are:

Precompiled execution

There’s no expensive compiling step necessary during the execution, just by creating the procedure. During the create command the code will be parsed. In this step the logic will be separated from the SQL statements. The procedural logic gets turned into bytecode (stored in SYSCAT.ROUTINES), the SQL statments get bound into a package (SYSCAT.PACKAGES).

Reducing network traffic

In a stored procedure you’re just sending some less input and output parameters over the network. Long SQL queries and code will not be transmitted over the wire.

Efficient reuse of code and programming abstraction

Stored procedures can be used by multiple users and client programs. Stored Procedures itself can be called by other stored procedures.

Enhanced security controls

You can grant users permission to execute a stored procedure independently of underlying table permissions.

Easy to learn

The SQL Procedural Language is very easy to learn and to understand. It’s similar to the constructs in a lot of other programming languages and the syntax is very simple.