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

Save the procedure

Perform a Deploy

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

Run the procedure and check the results.