Skip to Content
0
Sep 15, 2014 at 01:58 PM

How to work with table parameter in hdbprocedure format?

286 Views

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