Problem with Table Types

[Former Member]

Hi All,

I am creating a procedure with a table type as an OUT Parameter. As I cannot create Table types via the repository, I create it via the SQL Console.

When I first create the procedure and refer to the table type it works great. When I change the procedure, it drops the table type too. So when it tries to create the procedure it complains that the table type is not found.

Here is the sample code

On the SQL Console

create type XXX_SCHEMA.table_123 as table (

  user_id nvarchar(100) )

On the repository in file mytest123.procedure

CREATE PROCEDURE XXX_SCHEMA.mytest123 ( out names XXX_SCHEMA.TABLE_123 )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

names = select 'XXX' AS "USER_ID" from dummy;

END;

My question is - Can we use table types as shown above or is this not allowed in HANA. If it is allowed, how do I make sure that the table type does not get deleted when I modify a procedure. The basic reason we are using table types is because we have multiple procedures that will return data in the same format.

Venkatesh

[thomas_jung]

>As I cannot create Table types via the repository

Why not create them in the repository. Use hdbstructure or in SP6 you can also use hdbdd.

[Former Member]

Hi.

How do you change the procedure?

[Former Member]

Hi Venkatesh,

You can use table types as your example.

could you try "select 'XXX' AS "USER_ID" into names from dummy;", ?if it can return different result in multiple procedure.

Regards,

Jerry