Use Index-Based Cell Access

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

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

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;

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