Nested Stored Procedures

[former_member203890]

Does anyone knows about best practices for nested calls in store procedures?

An example:

SP_1(PARAM1)

{

Do something;

}

SP_2(PARAM2)

{

SP_1(PARAM2);

}

SP_3(PARAM3,PARAM4)

{

SP_2(PARAM3);

SP_2(PARAM4);

}

is or isnt a good practice?

THANKS.

[pfefferf · Accepted solution]

There is no real communicated disadvantage using nested procedure calls. As described in the online help (Declarative SQLScript Logic - SAP HANA SQLScript Reference) the dataflow graph of nested procedure calls are merged with the dataflow graph of the calling procedure and the whole dataflow graph is then optimized.

One thing which is not done, is the combination of statements for nested procedure calls. If really needed it can be done using the "Inline" hint (HINTS: NO_INLINE and INLINE - SAP HANA SQLScript Reference). It should be used carefully, cause it can cause performance problems if not used right.

If you follow the general Best Practices for SQLScript then you are on a good way.

Regards,

Florian

[Former Member · Accepted solution]

Florian,

I am under the impression that nested stored procedures are the best practise since the concept of modular coding is still valid in SQL developments?

I asume this would be less 'best practice'..

Start Procedure

     Business Logic code block 1

     Business Logic code block 2

     Business Logic code block 3

     Business Logic code block 4

End Procedure

Then this... ?

Start Procedure

     Call Procedure with Business Logic 1

     Call Procedure with Business Logic 2

     Call Procedure with Business Logic 3

     Call Procedure with Business Logic 4

End Procedure

Best Regards

Jonathan Belliot

[pfefferf · Accepted solution]

From my point of view "Clean Code Principles" have to be considered here as well, therefore I have the same opinion of you. Already from a maintenance perspective most developers find it easier to have several well defined artifacts which an "acceptable" amount of code lines instead of having such "big bang" artifacts with hundreds of code lines.

Regards,

Florian

[Former Member · Accepted solution]

Florian,

I totally agree so I guess we can conclude that the answer to this post is 'yes' .

Is there a way how we can group stored procedures, like an ABAP class would group ABAP methods?

Or should the repository package be used for this ?

Best Regards

Jonathan Belliot

[michael_eaton3 · Accepted solution]

While reading this post and the pseudo code above, there is one aspect of this that hasn't been mentioned yet - whether the separate procedures/code blocks need to be executed in series or parallel.

Unless explicitly defined, the code blocks/procedures will be executed in parallel (although separate procedures can sometimes upset the optimiser).

Michael

[Former Member · Accepted solution]

Michael,

You mean to say that we can't process separate stored procedures in serie ?

The approach below would not work because i've used this many times and never faced any issues with it. Even tho the developments are not in a productive environment yet.

procedure 1 => return value 1

check value1..

procedure 2 => use value 1

Best Regards

Jonathan Belliot

[michael_eaton3 · Accepted solution]

Do not be worried!

By default, HANA will try and execute everything in parallel.  If there are hard dependencies in the logic (like in your example) then the optimiser will handle that correctly.

If there are dependencies that HANA couldn't possible work out, then you can explicitly force the execution to be in series.

For example, the following logic does not have a hard dependency and HANA could execute them in parallel even though functionally you might want them to be executed in series

     insert into table a

     select from table a

This is why HANA has an explicit way to force execution in series using SEQUENTIAL EXECUTION - https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d467407519101484f190f545d54b24/content.htm

It is a feature that can sometimes catch people out.

Michael

[Former Member · Accepted solution]

Michael,

I never  do 😉

That's what i thought, good & clear explenation.

Best Regards

Jonathan Belliot