How to work with table parameter in hdbprocedure format?

[former_member186148]

Hello SDN.

I have a stored procedure with inbound table parameter:

PROCEDURE "MY_SCHEMA"."demo.dicts.sql::test" (
  IN DICT_DATA MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc"
)
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
  --DEFAULT SCHEMA <default_schema_name>
  READS SQL DATA AS
BEGIN

where MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc" is a defined type in HDBDD file:

...
type tt_common_dict_proc{
     ID: DictId;
     TYPE_ID: DictId;
     PARENT_ID: DictId;
     NAME: String255;
     CODE_VALUE: DictCodeValue;
     SHORT_NAME: String60;
};
...

Please note that I use *.hdbprocedure syntax instead of CREATE PROCEDURE as it recommended for SP7.

I try to call my procedure from SQL console with these commands:

drop table #test1;
create local temporary table #test1 like "MY_SCHEMA"."demo.dicts.db::DATA.tt_common_dict_proc";
insert into #test1 values('1', '', '', 'Dict name 1', 'CODE 1', 'Short name1');
insert into #test1 values('2', '', '', 'Dict name 2', 'CODE 2', 'Short name2');
insert into #test1 values('3', '', '', 'Dict name 3', 'CODE 3', 'Short name3');
call "MY_SCHEMA"."demo.dicts.sql::test"(#test1);

but getting the following error:

Could not execute 'call "MY_SCHEMA"."demo.dicts.sql::test"(#test1)' in 18 ms 815 µs .
SAP DBTech JDBC: [1301]: numeric or value error:  [1301] "MY_SCHEMA"."demo.dicts.sql::test":
 line 29 col 2 (at pos 987): [1301] (range 3) numeric or value error exception 

It seems that in fact parameter DICT_DATA in procedure is not a table-type parameter.

How I can define a table-type parameter based on the type from CDS?

Regards, Lev

[RichHeilman]

In your CDS file, how is DictId defined?

Cheers,

Rich

[RichHeilman]

Also, post all of your code within the procedure.

Cheers,

RIch

[former_member186148]

There are definitions of used types:

type Guid32: String(32);
type DictId: String(10);
type DictCodeValue: String(12);
type DictStatus: String(1);
type String255: String(255);
type String60: String(60);
type BoolString: String(1);

type TypeHistory{
     CREATE_DATE: UTCTimestamp;
     CREATOR: String255;
     CHANGE_DATE: UTCTimestamp;
     EDITOR: String255;
};

type TypeDict {
     TYPE_ID: DictId;
     PARENT_ID: DictId;
     NAME: String255;
     CODE_VALUE: DictCodeValue;
     SHORT_NAME: String60;
     STATUS: DictStatus;
     HISTORY: TypeHistory;
};

type tt_common_dict{
     ID: DictId;
     DATA: TypeDict;
};

This is my procedure. Actually I just began to develop it and faced with problem of passing table parameter so my procedure is very small:

PROCEDURE "MY_SCHEMA"."demo.dicts.sql::test" (
IN DICT_DATA MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc"
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*****************************
  Write your procedure logic
*****************************/
DECLARE v_id VARCHAR(10);
DECLARE v_code VARCHAR(10);
DECLARE lv_code VARCHAR(10);

DECLARE CURSOR c_data for select * from :DICT_DATA;
-- here I need to loop over my table parameter
FOR c_data_row AS c_data DO
-- here will be check for duplicate DICT_DATA-code_value in DB table
END FOR;


END;

By the way, can you please tell me, is there a way in HANA to do like ABAP syntax "SELECT FROM (lv_tablename) INTO..." where lv_tablename is a local variable contains DB table name?

Regards, Lev