Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to work with table parameter in hdbprocedure format?

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

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question