SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




CRM Companies List
Web Based CRM Software


Create Sample SQLScript Cursor on SAP HANA Database


This SAP HANA SQLScript tutorial shows how to declare and create SQL cursor for HANA database programmers. It is important to avoid cursors in SQL developments as a principal because cursors are just like ABAP LOOP statements dealing with each record one by one.
On the other hand, for code performance considerations it is better to deal with data using set based operations instead of record based operations like executing SQL cursors in SQLScript codes on a SAP HANA database.

If there is no other way, SQL programmer can use SQLScript cursors in native HANA database developments or in SAP HANA AMDP procedures as shown in this tutorial.

SAP HANA Database SQLScript Cursor Sample for SQL Programmer

Basic syntax to create a SQL cursor on SAP HANA database SQLScript code, developers can use below cursor example.

A SQLScript cursor is defined by "DECLARE CURSOR" command following the cursor name and SELECT statement which is the source data of the SQL cursor.

DECLARE CURSOR cursorName FOR
SELECT ...

To loop through all the records or rows within the SQL cursor, database developers can use following template

FOR cursorRow AS cursorName
DO
-- sqlscript codes
END FOR;

Business logic related with the purpose of the SQL cursor construction is executed for each row of the cursor declaration statement one by one between FOR and END FOR statements.

SQLScript cursor

ABAP SQL programmers can see easily that by using cursorname.columnname format, it is possible to read column values of the current SQLScript cursor loop step.

DO
BEGIN

 DECLARE CURSOR cursorSample FOR
  select BUKRS from "SAPABAP1".T001 where mandt <> '000';

 declare vBUKRS nvarchar(256);
 declare vRowCount int;

 for cursorRow as cursorSample
 do
  -- logic with sqlscript code within cursor loop

  vBUKRS := cursorRow.BUKRS;
  -- select :vBUKRS from dummy;

  tblSalesOrg = select bukrs, vkorg from "SAPABAP1".TVKO
   where bukrs = :vBUKRS;
  select count(*) into vRowCount from :tblSalesOrg;
  if (:vRowCount > 0) then
   select
    cursorSample::rowcount as rownum,
    :vBUKRS as vBUKRS,
    :vRowCount as vRowCount,
    *
   from :tblSalesOrg;
  end if;

  -- sql code in cursor loop (END)
 end for;

END

While processing each row of the SQL cursor between FOR, END FOR statements SQLScript developer can get the current loop number by using cursorName::rowcount

SAP HANA database SQL cursor code sample

If you execute above SAP SQLScript cursor example code by modifying the SAP schema according to your database configuration, you can fetch some data displayed similar to ones in below screenshot. The output is displaying company codes with sales organizations and some cursor loop variables like row number within the cursor loop.

SQLScript cursor execution on SAP HANA database

ABAP programmers who build SQL scripts should remember the most important rule related with SQL Cursors: Avoid using cursor if possible

For example, the same output provided by the above sample SQL cursor query can be fetched by using following single SELECT statement

select tvko.bukrs, tvko.vkorg
from "SAPABAP1".T001 as t001
inner join "SAPABAP1".TVKO as tvko on t001.bukrs = tvko.bukrs
where t001.mandt <> '000'





SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems