Skip to Content

Use Index-Based Cell Access

test
0 %
Use Index-Based Cell Access
Details

Use Index-Based Cell Access

February 14, 2021
Created by
February 11, 2021
Leverage 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.
QR code

Prerequisites

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


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

    procedure editor
  2. Use what you have learned about procedure parameters, and define an output parameters as show here.

    out ex_products table (PRODUCTID nvarchar(10),
                           CATEGORY nvarchar(20),
                           PRICE decimal(15,2))
    
Log on to answer question
Step 2: Insert procedure code
  1. Between the BEGIN and END statements, using index based cell access, insert rows into an intermediate table variable using the following code.


    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;
  2. 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 "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
    
Log on to answer question
Step 3: Save, deploy and check results
  1. Save the procedure

    save
  2. Perform a Deploy

    save
  3. Return to the Database Explorer page and generate and run the CALL statement for this procedure.

    DBX
  4. Run the procedure and check the results.

    results
Log on to answer question

Next Steps

Back to top