Before going through the SQL scripting. Let us see the disadvantages of the HANA Data Modelling.

The Following are the disadvantages of the HANA Data Modelling:

1.Cannot Debug the views.

2.if there is complex requirement, the decomposition of the requirement is not possible

3.There is no possibility to write the any control flow logic, Orchestration logic inside of view

E.g.: IF…ELSEIF, FOR

4.SQL queries always return one result, changing of results are not possible or possible to some extent.

5.Not possible to handle any impressive logic. (Complex looping, Sorting, Searching etc.)

All the above can be overcome with the SQL scripting.

SQL Scripting: -

SQL Script is collection of extensions to SQL.

The purpose is to give procedural capabilities to SQL. It allows developers to write data intensive
logic inside of core DB. This is one of the techniques to achieve the code-to-data paradigm.

Ref link: help.sap.com/hana_platform

Types of statements:

1.Declarative logic or Data flow logic(Pure SQL statement)

a.There is high chance of parallelization in Declarative logic.

b.Code gets converted to Data Flow graph.

c.It has a strong chance of getting processed in parallel

2.Imperative Logic or Orchestration logic (IF, Looping,FOR,WHILE)

a.The HANA system comes out of the parallelization when the logic Is written in the
imperative logic.

What is Data Flow graph in SAP HANA?

E.g :-

--Q1

Lt_product = select * from snwd_pd where price > 1000;

--Q2

Lt_sales = select * from snwd_so where product_id in ( select prod_id from :lt_product);

--Q3

Lt_pruchase = select * from snwd_po where product_id in ( select prod_id from :lt_products);

--Q4

Select SUM(gross_amount) from :lt_sales;

What will kick-you-out of parallel mode

 Local variable or scalar parameter is in use, it will come out.(declare)

 Inside a processing block, system finds a DML / DDL.

 Encounters imperative logic (IF, LOOPING, CURSOR)

 SQL statements which are not assigned to implicit variable.

 Guidelines In case you cannot avoid any processing logic

1. Split the code where the logic which can be processed in parallel move-in to the separate
container.
2. Put all the imperative logic at the end of container.

Syntax for the Data type declaration in the SQL script:

E.g Declare variableName datatype;

Funda fox:

1.Every statement in SQL script ends with semi-column(;)

2.SQL script is not case-sensitive.

3. when we use a variable in SQL script, we have to put a colon before that (:).when you are assigning

A value to a variable we must avoid the coln.

E.g :

Declare x integer;

Declare y integer;

Y = :x;

The assignment variable in SQL script is := not just =.

E.g x :=5;

Data Type in SQL Script:

Numeric: TINYINT, SMALLINT, INT, BIGINT, SMALL-DECIMAL, DOUBLE, DECIMAL

CHAR: VARCHAR, NVARCHAR, ALPHANUM

DATA: TIMESTAMP, DATETIME

BINARY: VARBINARY

LARGE OBJECTS: CLOB, BLOB, NLOB

Data Variables:-

a. Scalar

b. Table Type (Implicit, Explicit)

c. Array (Single Column Table)

What is containers?

Ans: Container is nothing but block of SQL Script code. There are three type of container blocks

a. Anonymous SQL Block

Syntax: Do Begin

End;
E.g.: do (IN x integer => ? , IN y integer => ? ,OUT z integer => ?) begin
z := x + y;
select z from dummy;
end;
Disadvantages of Anonymous SQL Block:-
a.Cannot be used for the productive purpose
b.Bad Maintenance
c.Every time system run’s, it compiles it.
b. Stored Procedure: It is anonymous block which is pre-compiled and stored in DB
permanently. It can be reused by other containers. It can also accept IN, OUT, INOUT
parameters.

Syntax: CREATE PROCEDURE procname( IN p1

type, OUT p2 type, INOUT p3 type)
LANGUAGE SQLSCRIPT/R DEFAULT SCHEMA “schemaname” READS SQL DATA AS BEGIN
....
....
END;
c. UDF-User Define Functions: In this it always has input parameter no output parameters
And always return one value
Syntax: CREATE FUNCTION functionname( p1 type)
DEFAULT SCHEMA “schemaname” AS BEGIN
....
....
END;
SQL Scripting Statements Syntax:

DELETE:

DELETE FROM <TABLE NAME> WHERE <KEY> = <VALUE>;

For multiple entries :

DELETE FROM <TABLE NAME> WHERE <KEY> IN (VALUE1,VALUE2,VALUE3,VALUE4,….,VALUEN);

Exception Handling In SQL Script: -

In OOP’s ABAP  CX_ROOT (Super class of all exceptions in ABAP) CX_STATIC_ERROR

Exception handling based on the SQL Error Code :

Syntax:

Declare Exit Handler For SQL_ERROR_Code <Error Code No>

Select ‘Error Message ‘ from dummy;

Generic Exception Handling :

Syntax :

Declare Exit Handler For SQLException

Select ‘error message’ from dummy;

For Custom Message exception :-

Syntax:

Declare Exit handler sql_error_code <error_code>

Select ::SQl_Error_message from dummy;

Signal sql_error_code <Error_code> (> 10000> set message_text = ‘Error message’;

alter procedure genemployee(in x integer)

language SQLSCript default schema "RGURRALA"
as begin

declare n integer = 0;
declare exit handler for sql_error_code 10001
select ::sql_error_message from dummy;
if x = 0 then

signal sql_error_code 10001 set message_text = 'Enter the value greater than 1';

end if;

select Count(*) into n from RGURRALA.Employee;

if n > 0 then

declare i integer = 0;

for i in 1..x do

n = :n + 1;
insert into Employee values ( n, 'Employee ' || n);

end for;
else
declare y integer = 0;
create table Employee( EmployeeId integer , EmployeeName Varchar(100), primary
key(employeeId));

for y in 1..x do

insert into Employee values (y,'Employee ' || y);

end for;

end if;
end;
Table Types :

Table Type is a template of a table structure which can hold data( multiptle records) @runtime like
internal table.

1. Implicit creation of tables

2. Explicit creation
2.1 Creation on fly.
2.2 Creating using TABLE TYPE syntax

Syntax for table type creation:

Create type <tabletypename> AS Table ( col1 coltype,

Col2 cotype,
Col3 cotype);

CREATE TYPE anubhaviscrazy as table (

item_pos nvarchar(3),
size varchar(40),
qty decimal(15,2),
gross decimal(15,2),
currency nvarchar(5));
create procedure getOrderItemsByCat1 (in imCategory VARCHAR(60),
out out_order_items "ANUBHAV".ANUBHAVISCRAZY
)

Array: -

It is an single column table.Hold data of a single data type

Syntax: -

DECLARE arrayName arrayType ARRAY := ARRAY(val1, val2,val….3);

Access of data inside of an array is done by index of an array. Index here starts with 1.

Question:

Q: Can you return an array out of a procedure?

Ans: No.

Array example :-
create procedure arrayDemo (in idx integer, out ret integer)

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "SAPABAP1"

AS

BEGIN

declare tableOfTwo integer ARRAY = ARRAY(2,4,6,8,10,12,14,16,18,20);

ret = :tableOfTwo[:idx];

--can you return an array out of a procedure?

--NO

END;

Transfer data from an Array to a table

create procedure calculateTable (in num integer, out ret table(num integer))

LANGUAGE SQLSCRIPT

DEFAULT SCHEMA "SAPABAP1"

AS

BEGIN

declare i integer := 1;

declare calc integer ARRAY;

for i in 1..10 do

calc[:i] = :i * :num;

end for;

--transfer data from an Array to a table

ret = unnest(:calc) as (num);

--can you return an array out of a procedure?

--NO

END;
Cursor:
Cursors are used to fetch data from DB tables , they are actually a named query. Whenever a cursor

Is created, it is assigned /bound to a query. It is possible to also pass parameters to the query.

Steps to create the currency conversion using SQL Script:

** If there is any currency that can’t be converted can be handled by error_handling.

Error_handling = ‘Keep unconverted’ for reference see above fig

User Defined Functions:-

It is one of the containers used to define reusable code in SQL Script. Functions always return

One value.

*Functions can be created only in SQL script

*Functions never have bi-directional parameters like INOUT.

Types Of UDF(User Defined Functions):-

1.Scalar UDF  returns a scalar value, cannot use any table operations like cursor, CE, Array,
input can be table.
 Scalar UDF to calculate the area

create function getCircleArea(radius integer) returns

area decimal as

begin

area = ( 3.14 * radius * radius );

end;

select getCircleArea(10) from dummy

2.Table UDF  Returns a table value.

Example for table UDF:-

Use case: return the list of all the customers from snwd_bpa

Where bp_role = ‘1’

Example:

create function getCustomers returns

table(bp_id varchar(20), company_name varchar(80), currency_code varchar(4)) as

begin

return select bp_id, company_name, currency_code from sapabap1.snwd_bpa where bp_role = '1';

end;

**Note UDF can be called only using the select query. We cannot call the UDF using CALL Statement

*** Difference between the functions and procedures in SAP HANA?

Procedures Functions

Procedures are called by call statements Functions are always called by SQL
statemets

You can never call procedures in SQL queries They can be called also directly with
assignment operator

CircleArea = calculArea(10);//SPS 9
We have INOUT parameters in procedure Cannot have INOUT parameters

It can return multiple values Always returns one value either scalar
or table

Direct assignment of output to variable is not possible

AMDP:- AMDP stands for ABAP Managed Data Procedure

 It is just a method inside an AMDP class.

 What makes a class as AMDP class, when we add a special power to an normal class by adding
A maker interface, the class becomes AMDP class.Name of the interface is
IF_AMDP_MARKER_HDB
 A method should be created as (typically as static )inside the