Recursive procedure calls in SQLScript

Up to SAP HANA 2.0 SPS03 recursive calls in SQLScript were not allowed. This means that a procedure may not call itself either directly or indirectly via other procedures. This restriction was removed with SPS04. However, the call depth is limited to 32 and procedures in libraries also do not allow recursive calls. In the listing you see an example for the recursive calculation of the Fibonacci numbers. Please be careful, if you call this procedure with too large parameters (>20), it can quickly happen that the SAP HANA system reaches its limits.

CREATE PROCEDURE fibonacci(IN iv_value INT,
OUT rv_result INT)
AS BEGIN
DECLARE lv_tmp INT;

IF iv_value IN (1, 2)
THEN rv_result = 1;
ELSE
fibonacci( iv_value - 1 , rv_result );
fibonacci( iv_value - 2 , lv_tmp );
rv_result = rv_result + lv_tmp;
END IF;
END;

CALL fibonacci(20, ?);

Listing: Recursive calculation of the Fibonacci Numbers

Here is the link to SAP documentation on the topic. As far as I know, recursive queries are not supported.

This post is also available in: Deutsch

%d bloggers like this: