design a stored procedure for running some functions on Netteza database from Aginity workbench 4.3 on win7

Question

I need to design a stored procedure for running some functions on Netteza database from Aginity workbench 4.3 on win7.

CREATE OR REPLACE PROCEDURE my_pro(int)
RETURNS integer EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC

DECLARE t int; 
BEGIN
     t := 0 ;
     WHILE t <= 1 loop
         EXECUTE IMMEDIATE  'select 1';
         t := t + 1;
     END LOOP;
  END;
END_proc;

exec my_pro(0)

But I got null in result.

Did I miss something here ?

Thanks

Answer 1

You declared

CREATE OR REPLACE PROCEDURE my_pro(int)
RETURNS integer

but you did not 'RETURN' any integer value. You should just add a 'RETURN' statement with an integer value. For example:

CREATE OR REPLACE PROCEDURE my_pro(int)
RETURNS integer EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE t int; 
BEGIN
    t := 0 ;
    WHILE t <= 1 loop
        EXECUTE IMMEDIATE  'select 1';
        t := t + 1;
    END LOOP;
RETURN t;
END;
END_proc;