What is SQLScript?
SQLScript is a programming language that represents an extension of the SQL standard by SAP. This language was developed to transfer the execution of complex business logic from the application server to the SAP HANA database.
Thus the language SQLScript is comparable with the extensions other database vendors, such as PL/SQL from Oracle or SQL PL from IBM. All these extensions allow you to create a sequence of SQL statements is stored on the database server in the form of procedures or functions. These can then be started with a single call from the application server. In contrast to individual SELECT queries, the procedure calls can also be return several result sets.
The transfer of complex queries via SQLScript to the database server brings considerable advantages during execution. For example, the declarative formulation of queries in SQL allows massive optimization and parallelization of execution. In contrast, an imperative, dataset-wise programming on the application server does not allow such an optimization.
Nevertheless, imperative elements are also part of the SQLScript programming language. This allows the programming of algorithms and flow logic in procedures. A mixture of imperative and declarative logic is also possible, whereby optimization and parallelization is only possible to a limited extent.
With the AMDP Framework, SAP has created an almost seamless integration of database procedures into the ABAP language. This allows you to define, implement and call SQLScript procedures and functions directly in the ABAP source code. This allows ABAP developers without direct access to the SAP HANA database to take advantage of SQLScript and optimized execution.
The book SQLScript for SAP HANA gives a good introduction to the language. It is also available in English.
The reference documentation from SAP is definitely the most extensive resource in this area. A distinction is made between HANA SQL and SQLScript. In fact, the two together form a language, and the separation sometimes seems somewhat arbitrary. This is definitely the right reference if you are looking for details on individual statements or SQL functions.
Versions of SQLScript
With every new version of SAP HANA, the language is currently evolving. Accordingly, you always have to make sure that you have the right version of the documentation in front of you. The most important versions are up-to-date:
- HANA 1.0 SPS 12 – Highest SPS level of 1.0 and to be found on many older installations.
- HANA 2.0 SPS 03 – The most popular 2.0 version (11/2019)
- HANA 2.0 SPS 04 – latest version, available since 4/2019
The document What’s New in the SAP HANA Platform 2.0 contains the changes for each Support Package Level of 2.0, not only for SQLScript. A few new features of SQLScript in SPS04, which I will cover in my blog soon, are for example:
- Recursive procedure calls
- The EXISTS- and IN-Operators in SQLScript
- DML statements such as INSERT, UPDATE, and DELETE on table variables
- Run dynamic SQL as ReadOnly
- Embedded Functions in SELECT Queries
This post is also available in: Deutsch