cancel
Showing results for 
Search instead for 
Did you mean: 

How to input an array in store procedure

Former Member
0 Kudos

what is the way to import an array to store procedure , i tried the following but its giving a syntax error that statement not valid

PROCEDURE "ZTESTDEV"."ztest_development.search::proc" ( in it_pid VARCHAR(10) ARRAY)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  --DEFAULT SCHEMA <default_schema_name>

  READS SQL DATA AS

BEGIN

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Karthikeyan,

If you are trying to pass multiple values into a procedure, you can try using table type variable.

Regards,

Anil

nithinu
Participant
0 Kudos

Hi Anil,

Can you please explain this with a simple example..

Regards,

Nithin

Former Member
0 Kudos

Hi Nithin,

1. When you create an .procedure file to create a procedure, you can create a local table type which can be used inside that procedure alone to input/output multiple values. Below screenshot shows one such table type.

In the table type tab next to SQLScript, you can create a local table type by adding code

CREATE TYPE tt_test AS TABLE ( col1 VARCHAR(4), col2 DECIMAL, col3 INTEGER);


CREATE TYPE tt_test2 AS TABLE(out1 INTEGER);


You can use it as input or output as below :


CREATE PROCEDURE test (IN tt_test_in tt_test, OUT tt_out tt_test2) ...


This acts as an array locally to that procedure.


2. If you want to declare global table types, you can go for CDS approach. You can create a .hdbdd file with below sample code:


namespace sap.hana.practice.tabletype;

@Schema: 'SOME_SCHEMA'

context GlobalTypes { 

type tt_test

{ col1: String(10);

col2: String(40);

col3: Decimal(15,2);}; };


Upon saving and activating, this table type is available under corresponding schema -> procedures -> tabletypes section. You can drag and drop and use it in the required procedure as array globaly.


Regards,

Anil

Answers (2)

Answers (2)

Former Member
0 Kudos

UNNEST it into a table variable instead and pass it,

former_member226419
Contributor
0 Kudos

Hi,

I don't think so. I think you can explicitly declare array and play with the same.

For more help, refer sap help on array.

BTW what do you trying to achieve?

Br

Sumeet