---
parser: v2
author_name: Rich Heilman
author_profile: https://github.com/rich-heilman
primary_tag: products>sap-hana
tags: [  tutorial>intermediate, topic>sql, products>sap-hana, products>sap-hana\,-express-edition  ]
time: 10
slug: xsa-sqlscript-table-operators
canonical_url: https://developers.sap.com/tutorials/xsa-sqlscript-table-operators
---

# Using Table Variable Operators
<!-- description --> Leveraging SQLScript in Stored Procedures, User Defined Functions, and User Defined Libraries

## Prerequisites  
- This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud.
- **Tutorials:** [Using Index Based Cell Access](xsa-sqlscript-usingindexbased)


## You will learn  
- How to update procedure to use Table Variable Operators to manipulate the immediate table variables

## Intro
In this tutorial, you will update the previous procedure to now use Table Variable Operators to manipulate the immediate table variables.  We can use table variable operators to perform DML like operations on table variables without having to invoke the SQL layer.  In this exercise, we will experiment with the INSERT, UPDATE, DELETE, and SEARCH operators.

---

### Return to Previous Procedure

Return to the procedure called `build_products` in the procedure folder.

![procedure editor](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/1.png)



### Remove Previous Code

Remove the code inside the body, between the BEGIN and END statements leaving only the two lines shown here.

![remove](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/2.png)



### Enter Code for INSERT Operator

Use the INSERT operator to copy all rows of `lt_products` into the output parameter called `ex_products`. Then use the INSERT operator to insert 3 new products into the output table.  Since you are specifying the actual index, it will insert your new rows at that index and push all existing rows down.

![insert](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/3.png)



### Review Complete Code


The complete code should look very similar to this.

```
PROCEDURE "build_products" (
	  out ex_products table (PRODUCTID nvarchar(10),
        CATEGORY nvarchar(20),
       PRICE decimal(15,2) ) )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
BEGIN

 declare lt_products table like :ex_products;

 lt_products = select PRODUCTID, CATEGORY, PRICE from "MD.Products";
 :ex_products.INSERT(:lt_products);
 :ex_products.INSERT(('ProductA', 'Software', '1999.99'), 1);
 :ex_products.INSERT(('ProductB', 'Software', '2999.99'), 2);
 :ex_products.INSERT(('ProductC', 'Software', '3999.99'), 3);

END

```


### Save, Build, Run and Check Results


Use what you have learned and Save your work, perform a Build on the module. Then return to the Database Explorer and call the procedure.  In the Results tab, you should see the product data including the new products that you have inserted.

![results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/4.png)


### Enter Code for UPDATE Operator


Return to the procedure and further modify it.  Enter a DECLARE statement as shown. Also add a FOR loop to loop
through the rows and update the price using the UDPATE operator

![update](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/5.png)



### Review Complete Code

The complete code should look very similar to this.

```
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;
 declare lv_index int = 0;

 lt_products = select PRODUCTID, CATEGORY, PRICE from "MD.Products";
 :ex_products.INSERT(:lt_products);
 :ex_products.INSERT(('ProductA', 'Software', '1999.99'), 1);
 :ex_products.INSERT(('ProductB', 'Software', '2999.99'), 2);
 :ex_products.INSERT(('ProductC', 'Software', '3999.99'), 3);

 FOR lv_index IN 1..record_count(:ex_products) DO
   :ex_products.(PRICE).UPDATE((:ex_products.PRICE[lv_index] * 1.25), lv_index);
 END FOR;


END
```


### Save, Build, Run and Check Results

Use what you have learned and Save your work, perform a Build on the module. Then return to the Database Explorer and call the procedure.  In the Results tab, you should see the product data including the new products that you have inserted and now the prices have been increased.

![results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/6.png)


### Enter Code for DELETE Operator

Return to the procedure and further modify it.  Enter DECLARE statements as shown. Also add a FOR loop determine which lines to delete, and finally use the DELETE operator to delete the rows all at once.

![delete](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/7.png)




### Review Complete Code

The complete code should look very similar to this.
```
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;
 declare lv_index int = 0;
 declare lv_del_index int array;
 declare lv_array_index int = 0;

 lt_products = select PRODUCTID, CATEGORY, PRICE from "MD.Products";
 :ex_products.INSERT(:lt_products);
 :ex_products.INSERT(('ProductA', 'Software', '1999.99'), 1);
 :ex_products.INSERT(('ProductB', 'Software', '2999.99'), 2);
 :ex_products.INSERT(('ProductC', 'Software', '3999.99'), 3);

 FOR lv_index IN 1..record_count(:ex_products) DO
   :ex_products.(PRICE).UPDATE((:ex_products.PRICE[lv_index] * 1.25), lv_index);
 END FOR;

 FOR lv_index IN 1..record_count(:ex_products) DO
   IF :ex_products.PRICE[lv_index] <= 2500.00 THEN
    lv_array_index = lv_array_index + 1;
    lv_del_index[lv_array_index] = lv_index;
  END IF;
 END FOR;

 :ex_products.DELETE(:lv_del_index);


END

```



### Save, Build, Run and Check Results

Use what you have learned and Save your work, perform a Build on the module. Then return to the Database Explorer and call the procedure.  In the Results tab, you should see the product data that is left after the DELETE operation has been performed.

![results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/8.png)


### Enter Code for SEARCH Operator

Return to the procedure and further modify it.  Enter code for a new output parameter. Also add the two lines of code for performing a SEARCH on the intermediate table variable.

![search](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/9.png)



### Review Complete Code

The complete code should look very similar to this.
```
PROCEDURE "build_products" (
	        out ex_products table (PRODUCTID nvarchar(10),
                               CATEGORY nvarchar(20),
                               PRICE decimal(15,2) ),
            out ex_pc_productid nvarchar(10) )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   READS SQL DATA AS
BEGIN

 declare lt_products table like :ex_products;
 declare lv_index int = 0;
 declare lv_del_index int array;
 declare lv_array_index int = 0;

 lt_products = select PRODUCTID, CATEGORY, PRICE from "MD.Products";
 :ex_products.INSERT(:lt_products);
 :ex_products.INSERT(('ProductA', 'Software', '1999.99'), 1);
 :ex_products.INSERT(('ProductB', 'Software', '2999.99'), 2);
 :ex_products.INSERT(('ProductC', 'Software', '3999.99'), 3);

 FOR lv_index IN 1..record_count(:ex_products) DO
   :ex_products.(PRICE).UPDATE((:ex_products.PRICE[lv_index] * 1.25), lv_index);
 END FOR;

 FOR lv_index IN 1..record_count(:ex_products) DO
   IF :ex_products.PRICE[lv_index] <= 2500.00 THEN
    lv_array_index = lv_array_index + 1;
    lv_del_index[lv_array_index] = lv_index;
  END IF;
 END FOR;

 :ex_products.DELETE(:lv_del_index);

 lv_index = :ex_products.SEARCH("CATEGORY", 'PC', 1);  
 ex_pc_productid = :ex_products.PRODUCTID[lv_index];


END

```



### Save, Build, Run and Check Results

Use what you have learned and Save your work, perform a Build on the module. Then return to the Database Explorer and call the procedure.  In the Results tab, you should see the product data that is left after the DELETE operation has been performed in the first tab, and on the second results tab you should see the product id of the first row which category was equal to "PC".

![results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/xsa-sqlscript-table-operators/10.png)




---