Netezza Stored Procedure Return Resultset and working Example

  • Post author:
  • Post last modified:June 6, 2018
  • Post category:Netezza
  • Reading time:5 mins read

You can use the stored procedure to return the resultset in your applications. Typically, stored procedure returns a unique value, it can also return resultset in the form of tables. In this article, we will see Netezza Stored Procedure Return Resultset with an example.

Netezza Stored Procedure Return Resultset

Below are the steps you can follow to return resultset using stored procedure:

  • Create table that you will be using as a REFTABLE. The table specified in the RETURNS value must exist at the time that the stored procedure is created, although the table can be empty.

The table continues to exist after the stored procedure completes. You cannot drop the reference table while the stored procedure is defined. Drop procedure first to drop REFTABLE.

  • Define your stored procedure with a return value of “RETURNS REFTABLE (<table-name>)” to indicate that it returns a result set that looks like the specified table.
  • Inside the body of the procedure, use the variable REFTABLENAME to refer to the results table.

Read:

Netezza Stored Procedure Return Resultset Example

Below is the example to return resultset using Netezza stored procedure:

Create table to use as REFTABLE:

Before compiling stored procedure to return result set, you must create REFTABLE that will be used by Netezza to return result set to client. Below is the example of reference table that we will be using in Netezza stored procedure:

CREATE TABLE TAB1(ID INT, DEPT INT, NAME VARCHAR(20), SUB_CODE INT);

Nzplsql stored procedure to return resultset:

Below is the example of stored procedure that will make use of REFTABLE and return results to client. Without REFTABLE Netezza will not compile stored procedure:

https://gist.github.com/da82c466d733e13727b20e4a067e9c1e

If you check the REFTABLE once execution is completed, it will be empty. Netezza uses this as an reference table to return result set. It does not store data into REFTABLE.

Read:

Tags: ,