Skip to Content
0

call stored procedures from table functions

Sep 07, 2017 at 10:21 AM

596

avatar image

Dear Friends,

I am trying to call a stored procedure(Read only) inside a table function. I saw a SAP forum which says that it is possible to cal stored procedure inside table function and use its output inside table function.

https://answers.sap.com/questions/106249/use-of-stored-procedure-from-sps11-onwards.html

The Syntax which I am applying now:

BEGIN

/***************************** Write your function logic *****************************/

DECLARE TEMP TABLE;

RETURN TEMP = CALL "BIUSER"."GK1.Func_Proc::PR1" (?);

END;

I am getting error. Please suggest the right way to call.

Thanks,

Gaurav

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Sep 07, 2017 at 01:58 PM
1

You can call a procedure within a table function as long as the procedure is read only. But your way of calling a procedure is not right. You are expecting the result as returning argument of the procedure, but a procedure has only IN, OUT, INOUT parameters. So I expect you are working with an OUT parameter to return your result.

Just for demo purposes I assume that your OUT parameter is called ET_RESULT. You need to replace it of course by your real name. Also the local variable which points to the result needs to be typed to your type.

...
DECLARE myResult TABLE (
  ID integer,
VALUE nvarchar(255) ); CALL "BIUSER"."GK1.Func_Proc::PR1" (ET_RESULT => :myResult); ...

Regards,
Florian

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Dear Florian,

Thanks.

I followed your provided syntax, however I am still getting error. I am attaching the screenshots of my stored procedure and table function so that you can find out the gaps. Appreciate your help.

Procedure - It is running fine.

Table Function - It is giving error

Regards,

Gaurav

proc.png (12.8 kB)
tf.png (19.2 kB)
0

Do not combine RETURN with the CALL of the procedure. First Call the procedure, than you have the result in :temp. The return the values of :temp.

...
CALL "BIUSER". ... (RESULT => :temp); RETURN :temp; ...
1

Thanks Florian. That worked!!

So, just want to reconfirm the concepts from you.

1. Table function is introduced to replace Calculation views with SQL script. Hence, we should be able to do all kinds of operations, write all kinds of logic like looping, variables, SQL functions and finally return the result table with a RETURN statement. This means we can write n number of statements for our logic building inside table functions and finally return a final table using return statement.

2. Procedure on other hand will be only useful in Modeling if we can use them inside table functions as we cannot call procedure inside calculation view, also we cannot build a .hdb procedure with result view as yet.

Please comment and add if anything needed.

Thanks,

Gaurav

0

Regarding 1) Table functions were already part of the system and were not only introduced for the specific case you describe. But yes, table functions can be used as data sources in graphical calculation views and scripted calculation views should not be used anymore (they are deprecated).

Regarding 2) Not really sure what you are meaning, but yes, you can call a read-only procedure in a table function which is then used in a graphical calc. view.

0