Stored procedures with result set

With SAP HANA®, you can execute stored procedures returning a result set.

To do so, you must:
  1. Define a user type with CREATE TYPE, with the same structure as the result set.
  2. DECLARE a cursor to fetch the rows from the result view associated to the stored procedure (the name of the result view is defined by the WITH RESULT VIEW clause in CREATE PROCEDURE.
  3. The SELECT statement used by the cursor must specify named input parameters with the PLACEHOLDER keyword, and since parameter names must be enclosed in double quotes, you need to disable the Informix emulation with the fglhint_no_ifxemul special hint.
Note: Pay attention to VARCHAR / NVARCHAR translation done by the driver, to support non-ASCII character sets (the type must be created with NVARCHAR since the SQL translator does makes the conversion only for CREATE TABLE)
MAIN
   DEFINE i, n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   CREATE TABLE tab1 ( c1 INTEGER, c2 DECIMAL(6,2), c3 VARCHAR(200) )
   INSERT INTO tab1 VALUES ( 1, 123.45, 'aaaaaa' )
   INSERT INTO tab1 VALUES ( 2, 123.66, 'bbbbbbbbb' )
   INSERT INTO tab1 VALUES ( 3, 444.77, 'cccccc' )
   EXECUTE IMMEDIATE "create type type_tab1 as table (c1 INTEGER, c2 DECIMAL(6,2), c3 NVARCHAR(200))"
   EXECUTE IMMEDIATE "create procedure proc2( in key integer, out rs type_tab1 )"
                || " language sqlscript reads sql data with result view proc2_view as"
                || "  begin"
                || "  rs = select * from tab1 where c1 > :key;"
                || "  end"
   DECLARE curs CURSOR FROM "/* fglhint_no_ifxemul */ select * from proc2_view(placeholder.\"$$key$$\"=>?)"
   LET i = 1
   FOREACH curs USING i INTO n, d, c 
       DISPLAY n, d, c
   END FOREACH
END MAIN