Using Index Based Cell Access

Rich Heilman

July 17, 2020

Created by

September 5, 2016

Intermediate

10 min.

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.

Using index-based cell access to manipulate table data is faster than using cursors or arrays.

Step 1: Create a New Procedure

Use what you have learned and create a new procedure called build_products in the procedure folder.

Define an output parameters as show here.

Done

Log on to answer question

Step 2: Insert Into Table

Using index based cell access, insert rows into an intermediate table variable as shown here.

Done

Log on to answer question

Step 3: Check Complete Code

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[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

Done

Log on to answer question

Step 4: Save and Build

Click Save. Use what you have learned already and perform a build on your hdb module.