Stored procedures

Informix^® supports stored procedures with the SPL language, and with Java/ C as User Defined Routines.

Netezza^® supports stored procedures with the NZPLSQL language.

In Netezza (V6), a stored procedure must always return a value (see the RETURNS clause). The value returned from a stored procedure can be either a simple scalar value, or a result set (REFTABLE). Netezza has a limited support for stored procedures producing result sets (you must use dynamic SQL in the stored procedure). See the Netezza documentation for more details.

Note: Netezza does not support OUTPUT parameters for stored procedures, only one single value or a result set can be returned.

Solution

Informix stored procedures must be re-written in the Netezza language, and the call from programs is slightly different from Informix.

To call a stored procedure returning a simple scalar value, do following:

PREPARE s1 FROM "SELECT myproc(?,?,?)"
EXECUTE s1 USING var1, var2, var3 INTO res

To call a stored procedure returning a result set:

PREPARE s1 FROM "SELECT myproc(?,?,?)"
OPEN s1 USING var1, var2, var3
FETCH s1 INTO record.*
FETCH s1 INTO record.*
...

See SQL Programming for more details about executing stored procedures with Netezza.