Create a input parameter to call stored procedure

[Former Member]

Hi All,

i am creating input parameter in hana graphical represent calculation views.

while creating input parameter i selected derived from stored procedure.

after that i select stored procedure it was giving below error

* Procedures with output parameters of type Table not supported

The below code was my procedure

BEGIN

COLUMN_SEC=SELECT MAX(COLUMN_SEC) AS COLUMN_SEC FROM (

SELECT

CASE WHEN ROLE_NAME LIKE '%ALL%' THEN 1 ELSE 0 END AS COLUMN_SEC

FROM SYS.GRANTED_ROLES

WHERE GRANTEE = SESSION_USER);

END;

and i created out parameter as well as

Please help me.

Regards,
Srini

[pfefferf · Accepted solution]

Hello Srini,

for that parameter type you can only use a stored procedure (or scalar UDF) returning a scalar value. Your procedure will have a table typed output parameter which is not supported at the moment.

Regards,

Florian

[Former Member · Accepted solution]

Thanks Florian,

In this scenario may i know how do i create a stored procedure returning a scalar value.

please help me out.

regards,

Srini

[pfefferf · Accepted solution]

Here is a dummy example for a procedure returing a string 'Test' and the usage the procedure for a parameter in a calc. view. Personally I would use a scalar UDF for that.

[Former Member · Accepted solution]

Thanks Florian.

i created procedure as below

CREATE PROCEDURE "SOAR"."KS_COLUMN_TEST"(OUT COLUMN_SEC NVARCHAR(10))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

COLUMN_SEC := 'TEST';

END;

after that i am creating parameter in calc view
by that time it was thrown an error

* Procedure must have scalar parameter of type String as the output parameter

please find attachment for error details

Regards,
Srini

[pfefferf · Accepted solution]

The only difference which I can see between my and your example is, that I have created the procedure via the repository and you directly in the catalog.

On which HANA SPS you are working? I did it on SPS10. But I do not think that we have different SPS version, cause the option is (officially) only since SPS10.

[Former Member]

Hi,

I am facing weird issue. I have scalar stored procedure which is returning single value and using that procedure in input parameter with an option 'Derived from Procedure'.

I called this procedure and could see single value as an input whereas input parameter created in calculation view is not returning any value. Output data type of my procedure is varchar. Any guess what is missing here?

[Former Member]

Hi,

same thing happened to me also.

please help me out for this scenario

Regards,
Srini

[Former Member]

Hi Srini,

Are you still facing the issue, can I know which studio and DB version are you working with?

Regards,

Shireesha

[Former Member]

Hi Srini,

Make sure there is no authorization issue in your system, you can execute below SQL and check again,

GRANT EXECUTE ON SCHEMA <YOURSCHEMA> TO _SYS_REPO WITH GRANT OPTION;

Regards,

Shidong