You can also use imperative logic in scalar UDFs, to the extent that this does not conflict with the statements above. Imperative language constructs allow the developer to control data and control flow, for example loops, scalar variables and IF-THEN-ELSE statements.

 

Imperative Logic in SQL Script

• Imperative logic allows you to control the flow of the logic:
• Scalar variable manipulation
• Branching logic, for example using IF-THEN-ELSE
• Loops – WHILE and FOR
• DDL statements and INSERT, UPDATE, and DELETE statements
• Imperative logic is executed exactly as scripted and is procedural. It prevents parallel processing.

 

As we will be using the recommended approach using a source file to define our functions, lets look at this now.

 

 

A function should always be created as a source file in the Development view of Web IDE. Click on a project folder that belongs to HDB module and use the menu option New->Function. You don’t need to specify the extension .hdbfunction as this is automatically appended to the name you provide for the file.

 

The file opens with basic shell code for you to complete.

 

When you are finished writing the code, you simply save then build the object. Assuming you have no errors, a runtime function is created in the container that belongs to the project. You can then open the SQL console against the container and write some SQL to test your function.

 

If the function does not behave the way you expected, you can then enjoy the benefit of having a source file where you simply modify the code in the source file, re-save and re-build before re-testing. If you were using SQL statement directly to create the function, you would have to hope you saved the SQL code somewhere, else you would have to begin again and re-enter it all over.

 

Table User-Defined Functions

Table UDFs support the followings:

• They can have any number of input parameters.
• They return exactly one table
• They allow you to perform table operations within the body
• They are used in the FROM clause of SELECT statements.
• They must be free of side-effects, i.e. DDL statements or the DML statements INSERT, UPDATE and DELETE cannot be used in function bodies.

 

Syntax:

CREATE FUNCTION <function name> (<list of input parameters with type>)
RETURNS TABLE [table type | (<list of table column definitions>)]
AS
BEGIN
<function body>
RETURN <expression to set return table>
END;

 

 

Dynamic filtering

When defining table UDFs or stored procedures, a useful feature is dynamic filtering using the built-in table function APPLY_FILTER. This function allows you to apply a dynamic WHERE clause to a database table or table variable and assign the result to a table variable, as shown below:

 

 

By providing parameters for the test, you can apply a different WHERE to the same table on subsequent calls of the function, as shown in the Dynamic Filtering Argument WHERE Clause.

 

 

 

Execution Errors

There are variety of methods to keep an error on execution of a procedure, function, or SQL Script block from causing an uncontrolled abort. The first method of dealing with these situations is the EXIT HANDLER. In general, when an EXIT HANDLER is declared, the following behavior is followed:

• An error is encountered in procedure execution.
• Statements in the same execution block following the error are skipped.
• The actions specified in the EXIT HANDLER are executed.

 

The DECLARE EXIT HANDLER command can be used to react to specific error codes. It can also be defined as generic and used to respond to any error.