SAP HANA Procedure

5/7/2015 SapHanaTutorial.Com HOME » SAP HANA Procedure Learning­Materials HANA Basics http://saphanatutorial.com/sap­hana­procedure/ 1/21 5/7/2015 » SAP HANA Procedure Administration SQLScript & Procedure Data Modeling http://saphanatutorial.com/sap­hana­procedure/ 2/21 5/7/2015 » SAP HANA Procedure Cloud Hadoop Hadoop Overview MapReduce http://saphanatutorial.com/sap­hana­procedure/ 3/21 5/7/2015 » SAP HANA Procedure Yarn Interview­Q&A Certifications Quiz SAP HANA Overview http://saphanatutorial.com/sap­hana­procedure/ 4/21 5/7/2015 » SAP HANA Procedure SAP HANA Architecture SAP HANA Modelling http://saphanatutorial.com/sap­hana­procedure/ 5/21 5/7/2015 » SAP HANA Procedure SAP HANA Studio Online­Courses HANA HANA Basics HANA Advanced Modelling http://saphanatutorial.com/sap­hana­procedure/ 6/21 5/7/2015 » SAP HANA Procedure Fast Track to HANA HANA Administration Part­1 Hadoop Hadoop Basics Hadoop Installation on Windows Forum Puzzles Explore The World of Hana With Us About Us http://saphanatutorial.com/sap­hana­procedure/ 7/21 5/7/2015 » SAP HANA Procedure Contact Us Home About Us Contact Us Learning Materials Interview Q & A http://saphanatutorial.com/sap­hana­procedure/ 8/21 5/7/2015 » SAP HANA Procedure Certifications Quiz Online Courses http://saphanatutorial.com/sap­hana­procedure/ 9/21 5/7/2015 » SAP HANA Procedure Apps Forum http://saphanatutorial.com/sap­hana­procedure/ 10/21 5/7/2015 » SAP HANA Procedure Apps http://saphanatutorial.com/sap­hana­procedure/ 11/21 5/7/2015 » SAP HANA Procedure HANA App Hadoop App ';  Search Search Topics Hadoop Big Data Need to Brush up on Hadoop Skills? Get the Free Field Guide to Hadoop. Topic Index   + SQLScript + Tables in SAP HANA ­ Procedure Introduction and Overview Create Procedure Without Using SQL Console http://saphanatutorial.com/sap­hana­procedure/ 12/21 5/7/2015 » SAP HANA Procedure + Examples of Procedure 0 Like 182 0 4 SAP HANA Procedure ­ Old Wine in New Bottle! In this article we will explain What is SQL Procedure? What are the different ways to create procedure in HANA? The pros and cons of procedure in HANA What is Procedure? 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 input 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) Few more facts on procedure: The body of a procedure consists of a sequence of statements separated by semicolons An intermediate variable, inside a procedure, is not required to be defined before it is bound by an assignment A variable name is prefixed by ':' while used as input to another statement Cyclic dependencies that result from the intermediate result assignments or from calling other functions are not allowed A Procedure can be created using the SQL editor or using creation wizards available for the different perspectives in the SAP HANA Studio (Modeler and Development perspectives) Different ways to create a procedure: There are 3 ways to create a procedure in HANA. Using the SQL editor Using the Modeler wizard in Modeler perspective Using SAP HANA XS project in "SAP HANA Development" perspective We will learn about each of these approaches in details. Note: Do not get confused by the questions like ­ why so many ways to create procedure? Which is one should I use? Which one is better? We will explain these later. Right now let us just learn each of these approaches. Prerequisites: Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that. Example Scenario: Suppose there are 3 tables PRODUCTS, REGION and SALES. http://saphanatutorial.com/sap­hana­procedure/ 13/21 5/7/2015 » SAP HANA Procedure We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter. We will create a procedure to achieve this. Create Tables: Open HANA Studio and expand the SAP HANA system. Go to your schema. Right­click on your schema and select SQL editor. Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query. create schema <schema_name>; Copy and paste the below script in SQL editor and execute. http://saphanatutorial.com/sap­hana­procedure/ 14/21 5/7/2015 » SAP HANA Procedure ­­­­REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME ­­­­ Create Product table create column table "<YOUR SCHEMA>"."PRODUCT"( "PRODUCT_ID" INTEGER, "PRODUCT_NAME" VARCHAR (100), primary key ("PRODUCT_ID") ); insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts'); insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets'); insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers'); insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats'); insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse'); ­­ Create Region table create column table "<YOUR SCHEMA>"."REGION"( "REGION_ID" INTEGER, "REGION_NAME" VARCHAR (100), "SUB_REGION_NAME" VARCHAR (100), PRIMARY KEY ("REGION_ID") ); insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North­America'); insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South­America'); insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India'); insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan'); insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany'); ­­ Create Sales table create column table "<YOUR SCHEMA>"."SALES"( "REGION_ID" INTEGER , "PRODUCT_ID" INTEGER , "SALES_AMOUNT" DOUBLE, PRIMARY KEY ("REGION_ID", "PRODUCT_ID") ); insert into "<YOUR SCHEMA>"."SALES" values(100,1,100); insert into "<YOUR SCHEMA>"."SALES" values(100,2,90); insert into "<YOUR SCHEMA>"."SALES" values(100,5,85); insert into "<YOUR SCHEMA>"."SALES" values(200,2,80); insert into "<YOUR SCHEMA>"."SALES" values(200,1,75); insert into "<YOUR SCHEMA>"."SALES" values(300,3,85); insert into "<YOUR SCHEMA>"."SALES" values(400,4,75); insert into "<YOUR SCHEMA>"."SALES" values(500,1,65); insert into "<YOUR SCHEMA>"."SALES" values(500,2,65); After executing the scripts you should have 3 tables created. If there are no tables, try right­clicking on your schema and refreshing. http://saphanatutorial.com/sap­hana­procedure/ 15/21 5/7/2015 » SAP HANA Procedure Grant schema SELECT rights to _SYS_REPO user: Open the SQL editor of your schema and execute the following command line: GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION; If you miss this step, an error will occur when you activate your views later. Click here to know more about this. Create Table Type: We need to create a table type, which will be used for output parameter of the procedure. Execute the below SQL statement. ­­REPLACE SCHEMA_NAME WITH YOUR SCHEMA CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE ( SALES_AMOUNT DECIMAL, NET_AMOUNT DECIMAL, PRODUCT_NAME NVARCHAR(20), REGION_NAME NVARCHAR(20), SUB_REGION_NAME NVARCHAR(20) ); To know more about Table Type, check the article SAP HANA Table Type. Create Procedure Using the SQL editor: Syntax: CREATE PROCEDURE {schema.}name {({IN|OUT|INOUT} param_name data_type {,...})} {LANGUAGE <LANG>} {SQL SECURITY <MODE>} {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS http://saphanatutorial.com/sap­hana­procedure/ 16/21 5/7/2015 » SAP HANA Procedure BEGIN ... END READS SQL DATA defines a procedure as read­only. Implementation LANGUAGE can be specified. Default is SQLScript. WITH RESULT VIEW is used to create a column view for the output parameter of type table Steps to Create The Procedure: Open SQL Editor and execute the below script CREATE PROCEDURE SCHEMA_NAME."PROCEDURE_SALES_REPORT"( IN DISCOUNT INTEGER, OUT OUTPUT_TABLE SCHEMA_NAME."TT_SALES" ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS /*********BEGIN PROCEDURE SCRIPT ************/ BEGIN var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT FROM SCHEMA_NAME.REGION AS T1 INNER JOIN SCHEMA_NAME.SALES AS T2 ON T1.REGION_ID = T2.REGION_ID; var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME FROM :var1 AS T1 INNER JOIN SCHEMA_NAME.PRODUCT AS T2 ON T1.PRODUCT_ID = T2.PRODUCT_ID; OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT ­ (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT, PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME FROM :var2 GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME; END; Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there. http://saphanatutorial.com/sap­hana­procedure/ 17/21 5/7/2015 » SAP HANA Procedure How to call Procedure in HANA: We call procedure using CALL statement. Execute the below statement to call this procedure. CALL SCHEMA_NAME."PROCEDURE_SALES_REPORT" (10, null); For table output parameters it is possible to either pass a (temporary) table name or to pass NULL. The option NULL will display the output directly on the client output screen. Continue Reading: Create Procedure in HANA ­ Do Not Use SQL Editor Any More Hadoop Data Integration The only enterprise­class data integration natively on Hadoop http://saphanatutorial.com/sap­hana­procedure/ 18/21 5/7/2015 » SAP HANA Procedure Ask The Experts  What People Say About Us A resourceful website and the best part is hana online courses are offered free of cost Tanmoy Deb A very good site for learning SAP HANA http://saphanatutorial.com/sap­hana­procedure/ 19/21 5/7/2015 » SAP HANA Procedure Arindam Choudhury Explore More   2 thoughts on “SAP HANA Procedure” http://saphanatutorial.com/sap­hana­procedure/ 20/21 5/7/2015 » SAP HANA Procedure 1. Ashutosh Hans says: March 25, 2015 at 8:11 am How to call a procedure using xsodata.. please help Reply Admin says: March 26, 2015 at 4:36 pm Hi Ashutosh, You can not directly call a procedure from xsodata. You can wither wrap the procedure in a view and pass that in xsodata. Or use XSJS t call procedure. For XSJS procedure call check – Reply Leave a Reply Your email address will not be published. Required fields are marked * Name * Email * Website Comment Post Comment © 2015 : saphanatutorial.com, All rights reserved. http://saphanatutorial.com/sap­hana­procedure/ Privacy Policy 21/21