New SQLScript Features in SAP HANA 1.0 SPS 11

In SPS 11, we took a break from enhancing the current SQLScript tooling In the Web-Based Development Workbench and really just focused more on the SQLScript language itself.  As you will soon see, the overall theme of the new features for SQLScript is around simplifying the language and making it easier for developers to use.

TRUNCATE Statement Support

In previous releases, we did not support the TRUNCATE statement within the context of a stored procedure.  You were forced to use the DELETE statement, or even dynamic SQL to achieve this.  Now as of SPS11, you can use the TRUNCATE statement instead of DELETE.  The TRUNCATE statement is better because it is faster if you want to simply clear all rows of a table

ALTER PROCEDURE/FUNCTION Statement Support

In SPS10, we introduced the concept of HEADER ONLY procedures and functions.  This allowed you to issue CREATE PROCEDURE and CREATE FUNCTION statements which contained the header without the actual logic within the body.  This solved the problem of having inter-dependencies between objects and having to know in which order to create the objects via the SQL Console.  The ALTER PROCEDURE/FUNCTION statement was then used to inject the body of the procedure/function after the creation of the objects.  In SPS10, you could only use the ALTER PROCEDURE/FUNCTION statement against procedures/functions which were created using the HEADER ONLY extension of the CREATE statement.  In SPS11, we have lifted this limitation and you are now allowed to issue an ALTER PROCEDURE/FUNCTION statement against any procedure or function.

Filter Pushdown for Table UDFs with Imperative Logic

Filter pushdown for Table User Defined Fucntions(UDF) refers to the concept of consuming a table UDF via a SELECT statement with a WHERE clause.  The filter in the WHERE clause is pushed down into the table UDF execution so that the result set is filtered before it is materialized. We’ve always supported the ability to do filter pushdown with Table User Defined Functions(UDF), but there was a limitation where if you used imperative type logic within the body of your table UDF, then the filter pushdown could not happen.  Now as of SPS11, even if you have imperative type logic within the body, the filter pushdown is now executed before materialization.

HANA Spatial Support

As of SPS11, we now support Spatial types and functions within the context of stored procedures. We support the ST_GEOMETRY data type and SQL spatial functions to access and manipulate spatial data.  SQLScript also now supports the objective style function calls, which is needed for some spatial functions.  In the example below,  since the IM_POINT_1 input parameter has a type of ST_GEOMETRY, we can directly call the ST_DIRECTION function of that object.

System Variables for Current Objects

In some cases, we need to write some code which determines what the current object is, for example, the name of the procedure which is being executed, and the schema in which that object resides.  As of SPS11, we have two new system variables.  ::CURRENT_OBJECT_SCHEMA is used to return the schema name of the current procedure or function and ::CURRENT_OBJECT_NAME is used to return the name of the current procedure or function. In this example below, we are simply concatenating the current object schema and current object name and passing it to the output parameter.

Default Values for Table Variables and Parameters

Several SPSs ago, we released the ability to use the DEFAULT keyword when defining scalar input parameters. Since then you could assign a default value for any scalar input parameter, but not table input parameters.  As of SPS11, you can now assign a default value for a table input parameter as well. You can simply reference the name of a database table after the DEFAULT keyword. You can also use the DEFAULT keyword to assign a default tabular value within a DECLARE statement when defining an intermediate table variable.

As shown below, you can also use the keyword DEFAULT EMPTY to initialize a table parameter, this works for both input and output table parameters.  This is particularly helpful for creating procedures with an empty body. Creating a procedure with an empty body and and an output table parameter without DEFAULT EMPTY would lead to the error indicating that the output table parameter is not assigned.

IS_EMPTY Predicate for Table and Table Variables

Another new feature in SPS11, is a proper emptiness check predicate.  We can use the IS_EMPTY() predicate to determine if a table of table variable has any rows.  If the table has rows, it returns true, if otherwise it returns false.  You can use IS_EMPTY in conditions like in IF statements or WHILE loops.  Prior to SPS11, you had to do a SELECT COUNT(*), put that value into a scalar variable, and finally execute your IF statement checking if the variables is greater than 0.  Now your code becomes much more simpler, by simply using the IS_EMPTY() with in an IF statement, as shown below.

Index-based Cell Access for Table Variables and Parameters

One of my favorite new features in SPS11 is what we refer to as Index-based cell access.  This allows you to read and write to any cell of an intermediate table variable or parameter.  Prior to SPS11, you had to write cumbersome code using arrays to achieve the same thing.  Now it is much more simpler and cleaner.  You simply reference the name of the table variable and the column which you want to access separated by a dot(.), followed by the index of the row within brackets.  In the following example, we are simply adding rows directly to the output table parameter.

Injection Prevention Built-In Functions

When using dynamic SQL, SQL Injection is a very real problem and it is the responsibility of the developer to make sure he does everything he can to prevent it.  We do not recommend to use dynamic SQL within SQLScript, but there is nothing stopping you from doing so.  So to help with this, we have released a few new built-in procedures which the developer can leverage in his code.

For more information on all of these features, refer to the SQLScript Reference Guide.