Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  AMDP - ABAP Managed Database Procedures →  METHOD - BY DATABASE PROCEDURE 

AMDP - SQL Script for the SAP HANA Database

SQL Script is a script language, documented in the SAP HANA SQLScript Reference, and used to program SQLScript procedures in the SAP HANA database. It is the implementation language of an AMDP method that specifies the addition FOR HDB LANGUAGE SQLSCRIPT when the statement METHOD is used.

Other versions: 7.31 | 7.40 | 7.54


Notes

  • Before AS ABAP is able to manage SQL Script procedures on the SAP HANA database, it requires the following authorizations as a user of the database system:

  • Privilege Execute on the object GET_PROCEDURE_OBJECTS of the schema SYS

  • Privilege Execute on the object TRUNCATE_PROCEDURE_OBJECTS of the schema SYS

Missing authorizations can be detected using transaction SICK.

  • When used in AMDP, SQL Script replaces calls of corresponding database procedures using database procedure proxies and the statement CALL DATABASE PROCEDURE. The required procedure can either be implemented in full in an AMDP method or an existing database procedure can be called from an AMDP method. In this case, the AMDP method replaces the database procedure proxy. Database procedure proxies are still recommended for scenarios in which secondary database connections are used to access existing procedures SAP HANA database alongside the current central AS ABAP database.

AMDP Procedures in the SAP HANA Database System

The SQL Script implementation of an AMDP method with the name meth in an AMDP class class is saved by the ABAP runtime environment as an SQLScript procedure under the name CLASS=>METH in the database schema SAPsid of the SAP HANA database. Note that these names are case-sensitive when used in the database system. AS ABAP is responsible for all transports, administration, and access control. SQL Script procedures managed using AMDP can be accessed as follows:

  • Calls from ABAP
Calls of the associated AMDP method using the regular method call meth( ... ) in ABAP programs.
  • Calls from other AMDP procedures
An AMDP procedure implemented using SQL Script in the same class or a different class can call another AMDP procedure with the usual SQLScript syntax:
CALL "CLASS=>METH"( f1 => a1, f2 => a2, ... );
The called method must be specified after the addition USING of the calling method. The usual visibility rules from ABAP Objects apply. A database procedure implemented in a class cannot call a method implemented in a private AMDP method of a different class, unless a friendship exists between the classes.
  • Calls from regular database procedures
An SQL Script procedure created in the regular way in SAP HANA Studio or using Native SQL can call an SQL Script procedure implemented in an AMDP method, if this procedure exists in the database system. This is not recommended, however, since the AMDP procedures are managed in AS ABAP and consistency can only be guaranteed for other AMDP procedures from the same AS ABAP.
  • Access in SAP HANA Studio
Once the ABAP runtime environment has created an SQL Script procedure managed by AMDP in the database system, it is visible in SAP HANA Studio and can even be edited. This is not recommended, however, since changes like this are ignored by the implementation in the AMDP method and can be overwritten by the ABAP runtime environment at any time.

Syntax


The syntax of a SQL Script procedure written in SQL Script is exactly as described under
SAP Hana SQLScript Reference, with one exception: The character  at the start of a line indicates a
comment line, as in ABAP. When the procedure is saved in
the database system, the asterisk, , is transformed to the usual double hyphens, --.


All SQL Script operators CE_... that support views can be used. SQL Script operators, which do not support any views (for example,
 CE_COLUMN_TABLE only works with the technical property Column Store) cannot be used.

Notes

  • Support for ABAP comments introduced using * enables the use of change entries inserted by the Modification Assistant tool in ABAP programs. This property should not be used when creating AMDP methods in the regular way.

  • The syntax is checked only on an AS ABAP whose central database is a SAP HANA database. Here, a temporary database procedure is created that checks the syntax.

Parameter Interface

The parameter interface of an SQL Script procedure uses input parameters declared using IN, output parameters declared using OUT, and input/output parameters declared using INOUT‎. All parameters can be scalar and tabular. When used in the procedure, certain operand positions expect or allow the name of an input parameter to be prefixed with a colon (:).

The parameter interface of an AMDP method that implements an SQL Script procedure is transformed accordingly:

  • An input parameter of the method defined using IMPORTING becomes an input parameter of the procedure declared using IN.
  • An output parameter of the method defined using EXPORTING becomes an output parameter of the procedure declared using OUT.
  • If CHANGING is used to define a
  • scalar input/output parameter of the method, the parameter becomes an input/output parameter of the database procedure declared using INOUT and if it is used to define a
  • tabular input/output parameter of the method, it is transformed to a pair of input and output parameters declared using IN and OUT on the database, since SQL Script does not support INOUT parameters. The OUT parameter has the name of the CHANGING parameter and is used instead of this parameter in the AMDP procedure. The IN parameter is created with an internal name comprising the name of the CHANGING parameter and a postfix __IN__. The IN parameter is assigned to the OUT parameter before the start of the actual AMDP procedure. This transformation and the associated call are generally transparent for all ABAP developers. It becomes visible only when the database procedure is displayed, for example, in SAP HANA Studio or called from another database procedure (see the example].

Elementary and tabular method parameters become scalar and tabular parameters of the database procedure respectively. As well as the restrictions for the parameter interface of an AMDP method described under AMDP, the following restrictions apply in the implementation with SQL Script:

  • An input/output parameter declared using CHANGING cannot have the type string or xstring. An exception this rule are parameters that are typed with reference to the predefined type SSTRING ABAP Dictionary.
  • The length of a parameter typed with the type c or n is restricted to a maximum of 5000 characters.
  • Parameters of the types f, decfloat16, decfloat34, string, and xtring cannot be assigned a replacement value using DEFAULT, which means they cannot be optional parameters.

As shown in the following tables, the elementary ABAP types are mapped to the appropriate types in SQL Script. The tables distinguish parameters that reference a type defined in ABAP from those that reference a predefined type in ABAP Dictionary. No AMDP procedures can be created for SQL Script types that are not listed here.


Note

In SQL Script, tabular parameters are handled as temporary database tables. An internal table passed to SQL Script can be accessed there just like a database table.

Mapping with Reference to Types Defined in ABAP

Numeric Types

ABAP Type HANA Type Note
b SMALLINT SMALLINT is a two byte integer with sign
s SMALLINT SMALLINT is a two byte integer with sign
i INTEGER -
p, length leng with dec decimal places DECIMAL, length 2len-1 with dec decimal places -
decfloat16 VARBINARY, length 8 Cannot be interpreted as a number in the database and no calculations can be performed
decfloat34 VARBINARY, length 16 Cannot be interpreted as a number in the database and no calculations can be performed
f DOUBLE -

Character-Like Types

ABAP Type HANA Type Note
c, length len NVARCHAR, length len -
string NCLOB Not permitted for input/output parameters
n, length len NVARCHAR, length len A suitable casting to a numeric database type must be performed before calculations can be made in the database. Numeric results must be transformed back to the ABAP format.
d NVARCHAR, length 8 A casting to the DATE type of the database must be performed before date calculations can be made. SQL Script offers the predefined function to_dats.
t NVARCHAR, length 6 A casting to the TIME type of the database must be performed before time calculations can be made. SQL Script offers the predefined function to_tims.

Byte-Like Types

ABAP Type HANA Type Note
x, length len VARBINARY, length len -
xstring BLOB Not permitted for input/output parameters

Mapping with Reference to Types Defined in ABAP Dictionary

The predefined types in ABAP Dictionary are mapped like the associated ABAP types, with the following exceptions.

Dictionary Type HANA Type Note
DF16_DEC, length leng with dec decimal places DECIMAL, length 2len-1 with dec decimal places Value range on the database is less than in ABAP
DF34_DEC, length leng with dec decimal places DECIMAL, length 2len-1 with dec decimal places Value range on the database is less than in ABAP
DF16_SCL - Obsolete, not supported
DF34_SCL - Obsolete, not supported
SSTRING, length len NVARCHAR, length len -

Calls

As described above, SQL Script procedures managed in the SAP HANA database using AMDP can be called from ABAP programs and from other AMDP procedures. How they can be used is determined by the visibility of the AMDP methods. Recursive and cyclical calls are forbidden in the HANA database system, which means that

  • an SQL Script procedure cannot call itself
  • an AMDP procedure that implements an SQL Script procedure cannot call any database procedures that themselves call the AMDP procedure.

Furthermore, an AMDP procedure can call regular SQL Script procedures created in SAP HANA Studio or using Native SQL. These procedures cannot and must not be specified after the addition USING of the calling AMDP.

Examples

See AMDP - Examples.