Create Procedures and Batches

Procedures and batches enhance the security, efficiency, and standardization of SAP Sybase IQ databases.

Procedures store SQL statements in the database for use by all applications. They enhance the security, efficiency, and standardization of databases. User-defined functions are a type of procedure that return a value to the calling environment for use in queries and other SQL statements.

For many purposes, server-side JDBC provides a more flexible way to build logic into the database than SQL stored procedures.

Batches are sets of SQL statements that are submitted to the database server as a group. Many features available in procedures, such as control statements, are also available in batches.

Note: Use source control software to track changes to source code, and changes to objects created from source (including stored procedures), that you deploy to the database.

Procedures
User-Defined Functions
User-defined functions are a class of procedures that return a single value to the calling environment.
Batches
Control statements
There are several control statements for logical flow and decision making in the body of a procedure, trigger, or user-defined function, or in a batch. Available control statements include:
Structure of Procedures
The body of a procedure consists of a compound statement.
Procedure Results
Procedures can return either single or multiple rows of data.
Error and warning handling
After an application program executes a SQL statement, it can examine a status code. This status code (or return code) indicates whether the statement executed successfully or failed and gives the reason for the failure. You can use the same mechanism to indicate the success or failure of a CALL statement to a procedure.
Transactions and savepoints in procedures
SQL statements in a procedure are part of the current transaction. You can call several procedures within one transaction or have several transactions in one procedure.
Hiding the contents of a procedure, function, trigger, event, or view
To distribute an application and a database without disclosing the logic contained within procedures, functions, triggers, events, and views, you can obscure the contents of these objects using the SET HIDDEN clause of the ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER, ALTER EVENT and ALTER VIEW statements.
Statements allowed in procedures, triggers, events, and batches
Most SQL statements are acceptable in batches, with the exception of the following: