How to make a procedure using SQL Script.
In this tutorial, we will learn to use SQL for Looping and Input Variables. In the output of the procedure, we will try to pull data from a simple Date lookup table.
HANA SQl script is of collection SQL statement for HANA Database. It is related to store procedure but it allows the users to push more logic to the database.
We are going to create two input a variable which will dictate Start Date of the table and the Number of Rows that comprise the table.
Now we are going to define and create a date lookup table in a new SQL window that will be the target of procedures.
Creating Target table
Please execute the codes given below in order to create a date lookup table or target table:
CREATE COLUMN TABLE "DATE_LOOKUP" (
"ROW_NBR" PRIMARY KEY,
"CALENDER_DAY" DAYDATE,
"CALENDER_DAY_HOUR" SECONDDATE,
"TIMEOFDAY" VARCHAR(1)
);
Note: For "CALENDER_DAY_HOUR" there will be 24 row of data I for each hour of a day
Now after creating the target table we can start creating the procedure.
Creating Procedure
CREATE PROCEDURE BUILD_DATE_LOOKUP_TABLE (IN NMBRROWS, IN STRTDATE TEXT) () LANGUAGE SQLSCRIPT AS
-- '2006-01-01'
CNTR INTEGER
DT VARCHAR (10);
BEGIN
DELETE FROM DATE_LOOKUP WHERE ROW_NBR > 0;
CNTR := 0;
DT := :STRTDATE;
WHILE CNTR < :NMBRROWS DO
INSERT INTO DATE_LOOKUP
SELECT
:CNTR + 1,
ADD_DAYS (TO_DATE (:DT, 'YYYY-MM-DD'), FLOOR(:CNTR/24)),
ADD_SECONDS (TO_DATE (:DT, 'YYYY-MM-DD'), :CNTR*3600),
CASE MOD (:CNTR+1, 24; WHEN 0 THEN 'E' NEW 1 THEN 'S' ELSE '' END
FROM DUMMY;
CNTR:= CNTR +1
END WHILE;
Now Execute the above created prcedure in order to create the start procedure in our SCHEMA.
Syntax to call the start procedure
CALL BUILD_DATE_LOOKUP_TABLE(2400, '2006-01-01')
Note: The first parameter we entered represent the number of line start procedure is going to create and the next one is for the start date for the table.
Execute the above SYNTAX and refresh your SCHEMA you will see a DATE_LOOKUP table, Preview it
