Question Re: Execution of Stored Procedure using SELECT Statement in Technology Q&A

Execution of Stored Procedure using SELECT Statement

jyoti_senapati — Sun, 31 Jul 2016 10:20:53 GMT

Hi Experts,

Can a Stored Procedure execute from a Select statement in HANA

Example:-

SQL Stored Procedure MY_PROC(IN IP INTEGER, OUT MSG VARCHAR(10));

Can I execute above procedure using a select statement

like SELECT OUT_MSG FROM......

Please help me out here.

Regards,

Jyoti Prakash Senapati

Re: Execution of Stored Procedure using SELECT Statement

pfefferf — Sun, 31 Jul 2016 11:35:22 GMT

Hello Jyoti,

no, you cannot call a stored procedure from a SQL statement. If you wanna execute SQLScript coding from an SQL statement you have to use a User Defined Table Function.

With that you can write statements like SELECT * FROM myTableFunction().

Regards,

Florian

Re: Execution of Stored Procedure using SELECT Statement

jyoti_senapati — Sun, 31 Jul 2016 17:03:23 GMT

Hi Florian,

Is there any way to call a stored procedure from a SQL Script Calculation View.

Re: Execution of Stored Procedure using SELECT Statement

pfefferf — Sun, 31 Jul 2016 17:08:35 GMT

Hi,

you can call read-only procedures within a scripted calc. view. But you should avoid to use scripted calc. views if possible. The way in the future is to use graphical calc. views and table functions if SQLScript is necessary.

Regards,

Florian

Re: Execution of Stored Procedure using SELECT Statement

jyoti_senapati — Tue, 02 Aug 2016 08:54:11 GMT

Hi Florian,

Thanks for the information.

Need some thing that we can trigger a SQL Script Stored Procedure from BW.

So I thought keeping a SP inside a Script Calc View can help it.

Do you know any other way to achieve it ?

Re: Execution of Stored Procedure using SELECT Statement

pfefferf — Tue, 02 Aug 2016 09:59:47 GMT

Hello,

there are different ways how procedures or SQLScript can be called on BW. It is depending on what you wanna reach.

For instance you can

create ABAP Managed Database Procedures (containing your SQLScript logic and/or calling further procedures)
use AMDP transformations
use HANA procedures/functions in the Analysis process
...

Regards,

Florian

Re: Execution of Stored Procedure using SELECT Statement

jyoti_senapati — Tue, 02 Aug 2016 10:17:56 GMT

Hello,

Could please share any Document or link for Point 1 and 3.

It will be helpfull.

Re: Execution of Stored Procedure using SELECT Statement

pfefferf — Tue, 02 Aug 2016 10:35:57 GMT

1) AMDP - ABAP Managed Database Procedures - ABAP Keyword Documentation

3) SAP HANACreating an Analysis Process - Using the SAP HANA Database - SAP Library

Re: Execution of Stored Procedure using SELECT Statement

jyoti_senapati — Tue, 02 Aug 2016 11:35:54 GMT

Many Thanks Florian