Recursive procedure calls in SQLScript

Veröffentlicht am 5. November 2019 von

Jörg Brandeis

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.