I didn't come across in any such scenario where we have to use stored procedure instead of Calculation View, but I read many sites where it is mentioned. One can use Stored Procedure in complex scenarios, but I am confused which scenarios are meant.
Can anyone suggest me such scenarios where we have to use Stored Procedure instead of Graphical Calculation View?
If you are looking for the parents (or children) of an object for undetermined depth, you have to do many SELECTs in a loop.
If you use views, the loop has to be on ABAP side, causing many roundtrips between the application server and the DB.
Stored procedures are very beneficial in this case, as they can run the loops on HANA side. You only have to more the end result through the network.
Sidenote: you should be using CDS views instead of Calculation views, as they offer many benefits.
First of all they are used by SAP internally in S/4 products, making CDS the way of the present and future.
Also they are ABAP objects, transported together with the referencing ABAP coding.
In a stored procedure, or in an AMDP you can use a script code block which can contain more than a single SELECT statement. You can store temp tables storing outcomes of previous SELECT commands in that AMDP and use later for example.
AMDP enables developers to keep the business logic in it. But if you are using a view, you are generally limited with allowed functions with a single SELECT statement For example, I could not use TRIM function within a CDS view but can use in AMDP
