Procedure in SAP HANA

v  A procedure is a unit/module that performs a particular task. Procedures are reusable processing blocks, and describe a sequence of data transformations Procedures can have multiple inputs and output parameters (scalar or table types) DROP and CREATE statements are used to modify the definition of a procedure A procedure can be created as read only (without side-effects) or read-write (with side-effects)

v  Stored Procedures can return data in the form of output parameters (integer or character) or a cursor variable. It can also result in a set of Select statements, which are used by other Stored Procedures.

v  Stored Procedures are also used for performance optimization as it contains series of SQL statements and results from one set of statement determines next set of statements to be executed. Stored procedures prevent users to see the complexity and details of tables in a database. As Stored procedures contain certain business logic, so users need to execute or call the procedure name.

An SQL Procedure can be created at –

1.      At Schema Level(Catalog Node)

2.      At Package Level(Content Node)

Example:

Here I try to create a simple procedure to get the data by entering the order id.

Code:

create procedure "KABIL"."PROCEDURE_SUPERSTORE__ORDER_ID"(in O_id integer) asbeginSELECT  * FROM "KABIL"."SUPERSTORE_SALES_HISTORY"where "Order ID"= :O_id;end;

-- To drop procedure

drop procedure "KABIL"."PROCEDURE_SUPERSTORE__ORDER_ID";

-- To call procedure

call "KABIL"."PROCEDURE_SUPERSTORE__ORDER_ID"(613);

      

Result: