Skip to Content
0
Former Member
Aug 16, 2013 at 12:57 AM

Problem with Table Types

115 Views

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