cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedures in SAP HANA

Former Member
0 Kudos

Hi,

What are the differences between yhr stored procedures created in Catalog vs. Content in SAP HANA?

When would you use one vs. the other?

Thanks,

Hyun

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Hyun,

They has several different for catalog and content.

     1. you only can use SQL script in SP for catalog, but you can create visualization SP in content(for example, create input and output paramter in perspective).

     2. you can define appointed schema of SP in catalog, but you can not define schema in SP of content, it is default in schema(_SYS_BIC) so far.

We created SP in catalog basically, generally, it was created based on  customer and your project strategy or policy, if customer or project ask you to create SP in appointed schema not _SYS_BIC, I think that you only can create in catalog , besides, consider develop and maintain efficient and cost, it may uses catalog way, it is only my opinion.

Hope this can help you.

Regards,

Jerry

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>you can define appointed schema of SP in catalog, but you can not define schema in SP of content, it is default in schema(_SYS_BIC) so far.

As of SPS6, you can specify the schema for creation for the procedure when creating in the Repository (via the the new procedure editor that runs from an XS project).

The main difference is that when creating in the Repository you get a managed design time version of the procedure.  This means that you track version management changes and you have a central transport mechanism.  You can create a delivery unit which contains all your development objects and easily install that into another HANA system.  Also only Repository procedures in a project can be debugged. For these reasons, SAP recommends that you create your procedures in the Repository.

Former Member
0 Kudos
Hi Tomas,  Yes, I agree with you about design time procedure, and  I already used it in native application, so far it is fine.  Regarding as run time procedure, I think that it maybe catalog as well, it create by SQL console.  Thank you for your clearing information.  Regards, Jerry
oliver_sviszt
Explorer
0 Kudos

Hi Thomas,

We need to use procedures for PAK planning functions implemented in HANA. Those require us to use table types with the input and output parameters.

Within "Catalog" we can create the table types and procedure by executing SQL DDL statements in the SQL console. Like "CREATE PROCEDRE .... AS....". However in this case we have no source control (versioning) and transport possibilities.

Within "Content" if I create the procedure in the editor, I do not see any possibility to use the table type.

What is the proper solution for such a scenario? Can he create our PAK procedures using table types and still somehow benefit from source control?

Additionally - although this is a somewhat different topic - we face the issue that we have to hardcode ALL column names in our procedures. If there is suddenly a new column in the table (infocube / aggregation level) all of our procedures would need to be updated one by one.

Thanks,

Oliver

former_member182302
Active Contributor
0 Kudos

Hi Oliver,

We have output parameters i.e VAR_OUT for the procedures created under "Content". This is actually the table type that will hold your result set.

And for the other question , you have asked we have used cursor to get the column names dynamically using the table named "TABLE_COLUMNS" so that you need not mention all the column names but instead you can get the dynamically based on the input of "TABLE Name".

As i remember i had a discussion with @John Appleby in one of the forum discussion where he mentioned we can achieve the same without even using the cursor (Should be a better approach).

May be he will also see this post and share his views.

Regards,

Krishna Tangudu

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

You should use a project and not the content folder of the SAP HANA Systems View.  There you can create your procedures (via .procedure files in SP5 & 6 or via .hdbprocedure in SP7).  You can create your table types via hdbstructure or hdbdd (as of SP6).

oliver_sviszt
Explorer
0 Kudos

Hi Krishna,

Thank you for your answer.

Regarding output parameters:

We would need to reference a pre-created table type in dozens of procedures like so: (assuming we create the procedure using SQL statement within Catalog, with the following statements)


CREATE TYPE SAPSR3.ZDFC_A02 AS TABLE ( 

ZQUAN_SP1 DECIMAL (000017, 000003),

  ZDPLREV DECIMAL (000017, 000002),

...


CREATE PROCEDURE SAPSR3.ZDFC_COPY_ACTUALS( IN I_VIEW SAPSR3.ZDFC_A02,

   I_REF_VIEW  SAPSR3.ZDFC_A02,

OUT E_T_TABLE  SAPSR3.ZDFC_A02 )

 

  AS

  BEGIN

...

Adding a new output parameter in the procedure editor in HANA Studio within Content brings up a structure editor dialog, where I would need to list the columns of the output table with the appropriate types one by one. I see no chance to reference the table type here anymore.

Regarding dynamic column name selection:

Were you using dynamic sql - concatenated as a string and then executed with EXEC - to select dynamically the columns, or did you manage to do without EXEC? I fear EXEC would have a very negative effect on our performance, especially in our scenario where the procedure is used within the Planning Application Kit.

The list of columns in our case is auctually deterministic during development, because we know the structure of our table type (ZDFC_A02 in the above example). We just want to make sure, that if a new column is introduced, we do not have to update all of our dozens of scripts manually.

Probably I will write a program (a windows application) where I can enter "meta-SQL" and  have it "compiled" to the real SQL. Something like:

SELECT @@"a" = "b" * "c"@@ @@"d" = 1@@ @@OTHERS@@ FROM ...

being compiled to:

SELECT "b" * "c" AS "a", "b", "c", 1 AS "d", "e", "f" FROM ...

of course assuming that the program is aware of the available columns an their orders.

Cheers,

Oliver

oliver_sviszt
Explorer
0 Kudos

Hello Thomas,

Thank you, I will try to see how it works out. (If we need to go with our own "meta-SQL compiler" described in the post above, I guess we will not be able to use HANA Studio at all, but then we will introduce some versioning in our own tool.)

Oliver

Answers (4)

Answers (4)

0 Kudos

This message was moderated.

0 Kudos

Thank you..this info is useful

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Thank you for all the replies.  They were very helpful!

Hyun