| SQL adaptation guide For IBM Netezza 1.x / Data dictionary | |
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.
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.*
...