Insert Mulitple rows in a OUT parameter in a User-defined Table type HANA

Question

I am stuck at a place.

There is a procedure that checks for something and inserts into an table type upon successful determination of that condition.

But I can insert only once in the table type. Is there a way to insert again and again into the table type?

    PROCEDURE "hello"."helloWorld.db::sampleException" (OUT TRACE_RECORD "hello"."LogTrace" )
    LANGUAGE SQLSCRIPT AS
BEGIN

DECLARE i int;

select count(*) into i from "hello"."REGION";
IF :i > 1 then 
TRACE_RECORD = SELECT '1' AS "LogID", '1' AS "TraceID" FROM DUMMY;
 end if;
IF :i > 2 then 
TRACE_RECORD = SELECT '2' AS "LogID", '2' AS "TraceID" FROM DUMMY;
end if;

END;

What I get on executing the procedure is only the last record "2,2".

How can I insert both the records 1,1 and 2,2?

Note: I do not want to use Temporary Tables.

Any help on this..

Thanks.!

Editing the question a bit:

• I have to use Table TYPE (till the time there is no optimal way better than it)
• I have to insert more than 20-30 records in the table type.

Answer 1

Do you have to write this as a procedure? A table-valued function seems more suitable:

CREATE FUNCTION f_tables4 (in_id INTEGER)
RETURNS TABLE (
    "LogID" VARCHAR(400),
    "TraceID" VARCHAR(400)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    RETURN 
    SELECT t."LogID", t."TraceID"
    FROM (
        SELECT 1 AS i, '1' AS "LogID", '1' AS "TraceID" FROM DUMMY
        UNION ALL
        SELECT 2 AS i, '2' AS "LogID", '2' AS "TraceID" FROM DUMMY
    ) t
    JOIN (SELECT count(*) AS cnt FROM "hello"."REGION") c
    ON c.cnt > t.i
END