New SQLScript Features in SAP HANA 1.0 SPS 10

Enhancements to SQLScript Editor & Debugger in the SAP Web-Based Development Workbench

In SPS 10, the SQLScript editor and debugger in the SAP Web-based Development Workbench has been enhanced in several ways to help the developer be more productive.  We introduced the editor in SPS 9 with basic keyword hints, but in SPS 10, we’ve expanded this to include code snippets and semantic code completion very similar to what we introduced in the SAP HANA Studio in SPS 9.  Basically, if you want to construct a SELECT statement, you simply type the word SELECT and hit CTRL+SPACE.  You will then get a list of possible code snippets to choose from.

Select the snippet you wish to insert and hit ENTER, the code snippet is inserted into the procedure.  You can then adjust as needed.

Another feature that we’ve added to the SQLScript editor in the web-based development workbench is semantic code completion.  For example, if you need to call a procedure, you can simply type the word CALL and hit CTRL+SPACE, and you will get a drop down list of procedures. Simply double click on the object you want to insert.  This is context sensitive, so it works quite well in other statements as well.

With SPS 9, we introduced the ability to debug procedures within the web-based development workbench, but only from the catalog.  As of SPS 10, you can now debug design-time artifacts(.hdbprocedure files) as well.  You simply open the .hdbprocedure file and set your breakpoints.  You can then, right click and choose “Invoke Procedure” to run it from the SQL console.  The debugging pane is show and execution stops at your breakpoint.  You can then of course single step through the code and evaluate values.

Commit/Rollback

One of the many stored procedure language features that a developer expects in any database is the concept of COMMIT & ROLLBACK.  Up until now we did not support COMMIT/ROLLBACK in SQLScript.  As of SP10, we now support the use of COMMIT/ROLLBACK within procedures only, not for scalar or table User Defined Functions(UDFs). The COMMIT statement commits the current transaction and all changes before the COMMIT statement.  The ROLLBACK statement rolls back, the current transaction and undoes all changes since the last COMMIT. The transaction boundary is not tied to the procedure block, so if there are nested procedures that contain COMMIT/ROLLBACK then all statements in the top-level procedure are affected. For those who have used dynamic SQL in the past to get around the fact that we did not support COMMIT/ROLLBACK natively in SQLScript, we recommend that you replace all occurrences with the native statements because they are more secure.  For more information, please see the section on Commit & Rollback in the SQLScript Reference Guide.

Header Only Procedures/Functions

We’ve also introduced the concept of “Header Only” procedures/functions in SPS 10.  This is to address a problem when creating procedures/functions that are dependent on one another.  You can’t create the one procedure/function before the other. Basically this allows you to create procedures/functions with minimum metadata first using the HEADER ONLY extension.  You can then go back and inject the body of the procedure/function by using the ALTER PROCEDURE statement.  The CREATE PROCEDURE AS HEADER ONLY and ALTER PROCEDURE statements are only used in the SQL Console, not in design-time artifacts. Below is a sample of the basic syntax, for more information, please see the section on Procedure & Function Headers in the SQLScript Reference Guide.

CREATE PROCEDURE test_procedure_header( in im_var integer,

                                out ex_var integer ) as header only;

ALTER PROCEDURE test_procedure_header( in im_var integer,

                                out ex_var integer )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

   ex_var = im_var;

END;

SQL Inlining Hints

The SQLScript compiler combines statements in order to optimize code.  SQL Inlining hints allows you to explicitly enforce or block the inlining of SQL statements within SQLScript.  Depending on the scenario, execution performance could be improved by either enforcing or blocking inlining. We can use the syntax, WITH HINT(NO_INLINE) or WITH HINT(INLINE).  For more information, please see the section on Hints: NO_INLINE & INLINE in the SQLScript Reference Guide.

Multiple Outputs from Scalar UDFs

In SPS 8, we released the ability to call scalar functions in an assignment statement. But there was a limitation which only allowed you to return one output parameter per call.  In SPS 10, you can now retrieve multiple output parameters from a single call.

The following function output_random_numbers has two return parameters called ex_rand1 and ex_rand2.

CREATE FUNCTION output_random_number( )

        RETURNS ex_rand1 integer,

                 ex_rand2 integer

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER AS

BEGIN

ex_rand1 = ROUND(TO_DECIMAL(1 + (999999-1)*RAND()),2);

ex_rand2 = ROUND(TO_DECIMAL(1 + (999999-1)*RAND()),2);

END;

In this procedure, we will call the function and retrieve both return parameters in one call.

CREATE PROCEDURE test_scalar_function(

          OUT ex_x integer, OUT ex_y integer)

  LANGUAGE SQLSCRIPT

  READS SQL DATA AS

BEGIN

    (ex_x,ex_y) = output_random_number( );

END;

You can also, retrieve both values separately with two different calls, referencing the name of the return parameter.

CREATE PROCEDURE test_scalar_function(

         OUT ex_x integer, OUT ex_y integer)

  LANGUAGE SQLSCRIPT

  READS SQL DATA AS

BEGIN

    ex_x = output_random_number( ).ex_rand1;

    ex_y = output_random_number( ).ex_rand2;

END;

Table Type for Table Variable Declarations

In SPS 9, we introduced the ability to declare a table variable using the DECLARE statement.  At that point, you could only define the structure explicitly inline, and could not reference a table type from the catalog or from the repository. In SPS 10, you can now do so.  In the below example, LT_TAB is declared referencing a table type in a CDS(.hdbdd) file. 

CREATE PROCEDURE get_products( )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    DEFAULT SCHEMA SAP_HANA_EPM_NEXT

    READS SQL DATA AS

BEGIN

declare lt_tab “sap.hana.democontent.epmNext.data::MD.Products”;

lt_tab = select * from “sap.hana.democontent.epmNext.data::MD.Products”;

select * from :lt_tab;

END;

Anonymous Blocks

Finally,  the last feature I would like to introduce is the concept of Anonymous Blocks.  This allows the developer to quickly write and execute SQLScript code in the SQL Console without having to create a stored procedure.  This is very useful for trying out small chucks of code during development.  You can execute DML statements which contain imperative and declarative statements. Again there is no lifecycle handling(CREATE/DROP statements), and no catalog object.  You can also not use any parameters or container specific properties such as language, or security mode.  The syntax is very simple, you basically use the word DO, followed by a BEGIN/END block.  Then you simply put your SQLScript code in the BEGIN/END block and execute it.  For more information, please see the section on Anonymous Blocks in the SQLScript Reference Guide.