Open SQL & Native SQL in SAP ABAP

What are Open SQL and Native SQL?

The goal of this tutorial is not to teach you SQL or database concepts but to introduce you to the SQL diversity in ABAP

In ABAP/4 programming language, there are two types of SQL being used.

1. NATIVE SQL
2. OPEN SQL.

Open SQL allows you to access the database tables declared in the ABAP dictionary regardless of the database platform that the R/3 system is using.

Native SQL allows you to use database-specific SQL statements in an ABAP/4 program. This means that you can use database tables that are not administered by ABAP dictionary, and therefore integrate data that is not part of the R/3 system.

Open SQL consists of a set of ABAP statements that perform operations on the central database in the R/3 system. The results of the operations and any error messages are independent of the database system in use. Open SQL thus provides a uniform syntax and semantics for all of the database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any R/3 system, regardless of the database system in use. Open SQL statements can only work with database tables that have been been created in the ABAP dictionary.

Open SQL is the default choice in almost every program, so its command set is covered first.

Basic Open SQL Commands

• SELECT
• INSERT
• UPDATE
• MODIFY
• DELETE
• OPEN CURSOR, FETCH, CLOSE CURSOR

Example

TABLES SBOOK.

DATA C TYPE CURSOR,

WA LIKE SBOOK.

OPEN CURSOR C FOR SELECT * FROM SBOOK WHERE CARRID = 'LH '

AND CONNID = '0400'

AND FLDATE = '19950228'

ORDER BY PRIMARY KEY.

DO.

FETCH NEXT CURSOR C INTO WA.

IF SY-SUBRC <> 0.

CLOSE CURSOR C.

EXIT.

ENDIF.

WRITE: / WA-BOOKID, WA-CUSTOMID, WA-CUSTTYPE,

WA-SMOKER, WA-LUGGWEIGHT, WA-WUNIT,

WA-INVOICE.

ENDDO.

Output the passenger list for the Lufthansa flight 0400 on 28-02.1995:

Every one of these statements reports its result through two system fields, described next.

Open SQL Return Codes

All Open SQL statements fill the following two system fields with return codes.

💡 Tip: Always check SY-SUBRC immediately after the statement. A later ABAP command overwrites the field, and the original result is lost.

When a table lies outside the ABAP Dictionary, Open SQL cannot reach it, and Native SQL takes over.

Native SQL

As already mentioned, Native SQL allows you to use database-specific SQL statements in an ABAP program.

To use Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement.

Syntax

EXEC SQL [PERFORMING <form>].

  <Native SQL statement>

ENDEXEC.

There is no period after Native SQL statements. Furthermore, using inverted commas (“) or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.

In Native SQL statements, the data is transported between the database table and the ABAP program using host variables. These are declared in the ABAP program, and preceded in the Native SQL statement by a colon (:). You can use elementary structures as host variables. Exceptionally, structures in an INTO clause are treated as though all of their fields were listed individually.

As in Open SQL, after the ENDEXEC statement, SY-DBCNT contains the number of lines processed. In nearly all cases, SY-SUBRC contains the value 0 after the ENDEXEC statement.

⚠️ Warning: Native SQL bypasses the SAP table buffer and the automatic client handling of Open SQL. The client field has to be supplied by the program, and the statement only runs on the database platform it was written for.

Open SQL vs Native SQL: Key Differences

Both interfaces reach the same database, yet they differ in portability, buffering, and the objects they can address. The table below summarises the practical differences.

Open SQL is therefore the default, and the rules below keep it fast.

Open SQL – Performance Rules

To improve the performance of the SQL and in turn of the ABAP program, one should take care of the following rules-

Keep the Result Set Small

• Using the where clause
• If only one record is required from the database, use SELECT SINGLE whenever possible .

Minimize the Amount of Data Transferred

• Restrict the number of lines
• If only certain fields are required from a table, use the SELECT <field1> <field2> INTO … statement
• Restrict no of columns
• Use aggregate functions

Minimize the Number of Data Transfers

• Avoid nested select loops
• An alternative option is to use the SELECT .. FOR ALL ENTRIES statement. This statement can often be a lot more efficient than performing a large number of SELECT or SELECT SINGLE statements during a LOOP of an internal table.
• Use dictionary views
• Use Joins in the FROM clause
• Use subqueries in the where clause

Minimize the Search Overhead

• Use index fields in the where clause
• When accessing databases, always ensure that the correct index is being used .

Reduce the Database Load

• Buffering
• Logical databases
• Avoid repeated database access

Using Internal Tables to Buffer Records

• To avoid executing the same SELECT multiple times (and therefore have duplicate selects), an internal table of type HASHED can be used to improve performance.

FAQs