on 08-14-2013 3:17 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
>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.
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
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
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
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you..this info is useful
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you for all the replies. They were very helpful!
Hyun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.