Using Index Based Cell Access
Leveraging SQLScript in Stored Procedures, User Defined Functions, and User Defined Libraries
You will learn
- How to use index-based cell access to manipulate table data.
Rich HeilmanJanuary 5, 2021
- This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud.
Using index-based cell access to manipulate table data is faster than using cursors or arrays.
- Step 1
Use what you have learned and create a new procedure called
build_productsin the procedure folder.
Define an output parameters as show here.
- Step 2
Using index based cell access, insert rows into an intermediate table variable as shown here.
- Step 3
The completed code should look like the following.
PROCEDURE "build_products" ( out ex_products table (PRODUCTID nvarchar(10), CATEGORY nvarchar(20), PRICE decimal(15,2) ) ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS BEGIN declare lt_products table like :ex_products; lt_products = select PRODUCTID, CATEGORY, PRICE from "MD.Products"; lt_products.productid = 'ProductA'; lt_products.category = 'Software'; lt_products.price = '1999.99'; lt_products.productid = 'ProductB'; lt_products.category = 'Software'; lt_products.price = '2999.99'; lt_products.productid = 'ProductC'; lt_products.category = 'Software'; lt_products.price = '3999.99'; ex_products = select * from :lt_products; END
- Step 4
Click Save. Use what you have learned already and perform a build on your
- Step 5
Return to the Database Explorer page and generate and run the CALL statement.
- Step 6