Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Stored Procedures

In Mimer SQL you can define functions, procedures, methods, and modules, collectively known as stored procedures.

Mimer SQL stored procedures enable you to define and use powerful functionality through the creation and execution of routines. By using stored procedures, you can move application logic from the client to the server, thereby reducing network traffic.

Stored procedures are stored in the data dictionary and you can invoke them when needed.

For a complete and detailed discussion of stored procedures, see Mimer SQL Programmer's Manual, Mimer SQL Stored Procedures. For a complete and detailed discussion of methods, see Mimer SQL Programmer's Manual, User-Defined Types And Methods.

Mimer SQL stored procedures are based on the ISO standard for Persistent Stored Modules (PSM).

Routines - Functions, Methods and Procedures

The term routine is a collective term for functions, methods, and procedures. Functions and methods are distinguished from procedures in that they return a single value and the parameters of a function or method are used for input only. A function or method is invoked by using it where a value expression would normally be used.

Mimer SQL supports standard procedures and also result set procedures, which are procedures capable of returning the row value(s) of a result set.

Standard procedures are invoked directly by using the CALL statement and can pass values back to the calling environment through the procedure parameters.

In embedded SQL, result set procedures are invoked by declaring a cursor which includes the procedure call specification and by then using the FETCH statement to execute the procedure and return the row(s) of the result set.

In interactive SQL, a result set procedure is invoked by using the CALL statement directly and the result set values are presented in the same way as for a SELECT returning more than one row.

The creator of a routine must hold the appropriate access rights on any database objects referenced from within the routine. These access rights must remain as longs as the routine exists.

Routine names, like those of other private objects in the database, are qualified with the name of the schema to which they belong.


A module is simply a collection of routines. All the routines in a module are created when the module is created and belong to the same schema.

EXECUTE rights on the routines contained in a module are held on a per-routine basis, not on the module.

If a module is dropped, all the routines contained in the module are dropped.

Under certain circumstances a routine may be dropped because of the cascade effect of dropping some other database object. If such a routine is contained in a module, it is implicitly removed from the module and dropped. The other routines contained in the module remain unaffected.

In general, care should be taken when using DROP or REVOKE in connection with routines, modules or objects referenced from within routines because the cascade effects can often affect many other objects.

For more information, see Mimer SQL User's Manual, Dropping Objects from the Database, and the Mimer SQL User's Manual, Revoking Privileges.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
Mimer SQL Documentation TOC PREV NEXT INDEX