Skip to Content
avatar image
Former Member

How to input an array in store procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 26, 2015 at 12:05 PM

    Hi Karthikeyan,

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

    Regards,

    Anil

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member N. Uppila

      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

      pastedImage_0.png (15.7 kB)
  • avatar image
    Former Member
    Oct 26, 2015 at 09:53 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 27, 2015 at 08:08 AM

    UNNEST it into a table variable instead and pass it,

    Add comment
    10|10000 characters needed characters exceeded