Use Index-Based Cell Access
Leverage SQLScript in stored procedures, user defined functions, and user defined libraries.
Overview
You will learn
- How to use index-based cell access to manipulate table data.
Prerequisites
Prerequisites
- This tutorial is designed for SAP HANA Cloud.
- Tutorials: Debugging Stored Procedures
Steps
Intro
Using index-based cell access to manipulate table data is faster than using cursors or arrays.
Use what you have learned and create a new procedure called
build_productsin the procedure folder.
procedure editor Use what you have learned about procedure parameters, and define an output parameters as show here.
SQLScriptout ex_products table (PRODUCTID nvarchar(10), CATEGORY nvarchar(40), PRICE decimal(15,2))
Between the BEGIN and END statements, using index based cell access, insert rows into an intermediate table variable using the following code.
SQLScriptdeclare lt_products table like :ex_products; lt_products = select PRODUCTID, CATEGORY, PRICE from "OPENSAP_MD_PRODUCTS"; lt_products.productid[1] = 'ProductA'; lt_products.category[1] = 'Software'; lt_products.price[1] = '1999.99'; lt_products.productid[2] = 'ProductB'; lt_products.category[2] = 'Software'; lt_products.price[2] = '2999.99'; lt_products.productid[3] = 'ProductC'; lt_products.category[3] = 'Software'; lt_products.price[3] = '3999.99'; ex_products = select * from :lt_products;The completed code should look like the following.
SQLScriptPROCEDURE "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 "OPENSAP_MD_PRODUCTS"; lt_products.productid[1] = 'ProductA'; lt_products.category[1] = 'Software'; lt_products.price[1] = '1999.99'; lt_products.productid[2] = 'ProductB'; lt_products.category[2] = 'Software'; lt_products.price[2] = '2999.99'; lt_products.productid[3] = 'ProductC'; lt_products.category[3] = 'Software'; lt_products.price[3] = '3999.99'; ex_products = select * from :lt_products; END
Save the procedure
save Perform a Deploy
save Return to the Database Explorer page and generate and run the CALL statement for this procedure.
DBX Run the procedure and check the results.
results
Resources
Discussion
Share feedback on this tutorial or join the conversation in SAP Community.