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
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:

1. 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;

2. Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there.

3. 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.

Click here to go to Next Chapter. 5.4 Create procedure using the Modeler wizard