The following statements are not permitted in a stored procedure and lead to a translation error:
- statements in which global or static-local variables are used
- output to the screen, printer or an operating system file:
WRITE, READ
CALL FORM, EDIT, REPORT, QUERY
WRITEFILE, READFILE, OPEN, CLOSE
Module options MODULETRACE, SQLTRACE
- change of the current program:
SWITCH, SWITCHCALL
- calls of DB procedures:
CALL DBPROC
SQL ( DBPROCEDURE ... )
(From a DB procedure, further DB procedures are called using
the CALL PROC statement.)
- termination of a transaction:
SQL ( COMMIT ), SQL ( ROLLBACK )
The explicit opening and closing of subtransactions, by contrast, is possible (SQL(SUBTRANS BEGIN), SQL(SUBTRANS COMMIT) and SQL(SUBTRANS ROLLBACK) see the ADABAS Reference Manual).
In SQL statements database objects must be completely qualified with the name of the owner.
Dynamic SQL can also be used in stored procedures. But for a stored procedure with dynamic SQL, it is not possible to grant the access rights to the database objects when granting the execute privilege. In this case, the user calling the stored procedure must have all privileges required.
A DB procedure or a trigger can only be created when all the routines exist which are directly or indirectly called by it.
A DB procedure or a trigger can also call an SQL-PL routine if it satisfies the restrictions described here (see in this context the workbench options PROC CHECK and SQL CHECK).
A DB function is subject to the same restrictions as a FUNCTION. Functions cannot call routines (CALL, SWITCH, and SWITCHCALL statements), but only other functions (FUNCTION). Without more specifications, all functions called are expected in the standard library (STDLIB). The called functions are expected in another library if the LIB option is set accordingly in the calling DB function.
No SQL statements can be used in a DB function. ADABAS returns an error if the attempt is made to define a DB function that calls a FUNCTION with SQL statements.