cancel
Showing results for 
Search instead for 
Did you mean: 

Calling Procedure in SQL Script

Former Member
0 Kudos

Hi All

  I am trying to call procedure in SQL Script as below but I am facing syntax error near call

  var_out =call "_SYS_BIC"."zcrmcoeteam/PC_EFASION"(203,null);

Request you to provide your inputs in finding the way out to call a procedure from SQL SCript calculation view.

Thanks

Santosh Varada

Accepted Solutions (1)

Accepted Solutions (1)

henrique_pinto
Active Contributor
0 Kudos

Hi Santosh,

isn't your syntax error because you forgot to put simple quotes around 203? Should be '203'.

But, if possible, my suggestion would be that you create your Procedure with the "with result view" option, which will then create a column view associated to this procedure. You can then do normal SELECT on this CV in your calc view, such as:

var_out = SELECT * FROM "SCHEMA"."YOUR_COLUMN_VIEW" WITH PARAMETERS

               ( 'PLACEHOLDER' = ('$$ip_param1', '203'),

               ( 'PLACEHOLDER' = ('$$ip_param2', null') );

You can find this commented in the page 17 of the SQLScript guide (http://help.sap.com/hana/hana_dev_sqlscript_en.pdf😞

If a read-only procedure has exactly one table output parameter a RESULT VIEW can be specified. The name of the result view can be any valid SQL identifier. When a result view is defined for a procedure, it can be called from a SQL statement like a table or view reference as shown below.

Example:

CREATE PROCEDURE ProcWithResultView(IN id INT, OUT o1 CUSTOMER)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ProcView AS

BEGIN

  o1 = SELECT * FROM CUSTOMER WHERE CUST_ID = :id;

END;

Normally, procedures can only be executed via the call statement. By using WITH RESULT VIEW it is possible to query the result of a procedure as part of a SQL statement. Please notice that no variable references are supported in the WITH PARAMETERS clause and that all constants are passed as string constants (i.e. enclosed in single quotes). Also, parameter names are lower case independent from the original capitalization in the procedure signature.

Example:

SELECT * FROM ProcView WITH PARAMETERS

                                   ('placeholder' = ('$$id$$', '5'))

Former Member
0 Kudos

Hi Henrique

   Thanks for the Quick reply. The above works in case if we don't have any input parameters.But the statement  below producing an error

"SAP DBTech JDBC: [2048]: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$shopid$$ is not set."

SELECT * FROM "SCHEMA"."YOUR_COLUMN_VIEW" WITH PARAMETERS                 ( 'PLACEHOLDER' = ('$$ip_param1', '203'),             

( 'PLACEHOLDER' = ('$$ip_param2', null') ); 

  Also one more limitation with the approach suggested is when ever there is new data which is coming in from source systems I don't think the column view specified in the procedure definition   will contain new data inorder to ensure that the data is up to date before executing the calculation view we need to execute the procedure and then execute the calculation view.So I am looking for the way to call procedure from calculation view .

Request you to share your views regarding above two perspectives.

I am looking for one work around as to create procedure without input parameters and then filter it using input parameters in calculation views ,I have not tested this I will test this and update, still this won't overcome the limitation of current data , we need further inputs.Thanks for your inputs

Thanks

Santosh Varada


henrique_pinto
Active Contributor
0 Kudos

Hi Santosh,

The error you're getting is because you used exactly the code I had pasted. Please, you need to adapt it to your particular case and not just ctrl+c ctrl+v. For instance, replace '$$ip_param1$$' with your param's name, i.e. '$$shopid$$', delete the other if you don't have a second param etc...

Regarding your request, I think I understood it a little better. And from what I get from the guide I linked above, you can't just call it from within a calc view. But if you modify it a little bit and create the output as I mentioned above, by outputting to a column view, you will be able to use it in the calc.

Former Member
0 Kudos

Hi Henrique

  I have used the below statement only then I am facing the error as mentioned above

SELECT * FROM "TCS252996"."FASHIONVIEW" WITH PARAMETERS          

( 'PLACEHOLDER' = ('$$SHOPID', '203'),'PLACEHOLDER' = ('$$FASHION', 'null') );

but when I use the statement call pc_efashion('203',null); I get the result as desired.

When we use Procedure with input parameters the view we used in procedure statment will not be populated because it depends on the input parameters because of the dynamic nature the View does not contain in data , if we try to data preview  FASHIONVIEW then view we get the same error

"SAP DBTech JDBC: [2048]: column store error: search table error:  [34092] search on calculation

model requires parameters;Required variable $$shopid$$ is not set."

it is being propagated to every where we use the select statement  view .Request your ideas ,

Thanks

Santosh Varada

henrique_pinto
Active Contributor
0 Kudos

When calling, use $$ before and after the parameter's name. Also, use the parameter's name all in lower cases, i.e. '$$shopid$$' and '$$fashion$$'. Lastly, to pass null, don't use quotes.

Former Member
0 Kudos

Hi Henrique

  Thanks for your help, now I can view the data but still when I try to data preview the column view such as Fashion View which is specified in the procedure statement is throwin an error , It could be because of the dynamic nature of the procedure but the statement gives me a result, now I can move forward and , I am trying to call the procedure from ABAP client and get teh results into table for further processing. Will get back here incase pf further help.

Thanks

Santosh Varada

henrique_pinto
Active Contributor
0 Kudos

Hi Santosh,

great you can see the content. 😉
Regarding the column view, did you create the procedure with the "WITH RESULT VIEW" option? Check the document I had linked above for the proper syntax.

Former Member
0 Kudos

Hi Henrique

    Yes my procedure is with Result view , the problem is the View shows data when there are no input parameters, but when I use input parameters the re sult view gives error as mentioned .It could be because the input parameter changes every time ,because of the dynamic nature it is not able to hold the data.Request your ideas

  Though I am able to get the data on ABAP client side.

Thanks

Santosh Varada

henrique_pinto
Active Contributor
0 Kudos

If you wish to, paste here the code for your create procedure statement (simplify table column names, if you wish).

Former Member
0 Kudos

Hi Henrique

Below is the SQL Script for my  procedure .

create procedure PC_EFASHION

( IN SHOPID VARCHAR

  (20),OUT FASHION FASHION_TYPE

)LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW FASHIONVIEW AS

BEGIN TMP_FASHION = CE_OLAP_VIEW

("_SYS_BIC"."mypackage/AN_EFASHION",["ARTICLE_ID","COLOR_CODE","YR","CITY","SHOP_ID","MARGIN","AMOUNT_SOLD"])

;

FASHION = CE_PROJECTION

(:TMP_FASHION,["ARTICLE_ID","COLOR_CODE","YR","CITY","SHOP_ID","MARGIN","AMOUNT_SOLD"],' "SHOP_ID" =

'':SHOPID'' ')

;

END;

Thanks

Santosh Varada

henrique_pinto
Active Contributor
0 Kudos

Hi Santosh,

differently from you had said before, you just have one single input param, and not two!

You don't need to pass null on the 2nd one, since it's just an output param.

Just doing something like:

SELECT * FROM "TCS252996"."FASHIONVIEW" WITH PARAMETERS         

( 'PLACEHOLDER' = ('$$shopid$$', '203') );

should be fine.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello,

This is magical, THANKS!

However, I'm trying to figure out the C procedure below.  How can I pass a variable into the SELECT clause?  I've tried every variation and concatenation I can muster, but the below is the only result that will validate ... problem is it always just returns ":inVal" as the passed value.

-Thanks, Mike

-- RUN this CREATE statement first to make it work

--create type tt_TEST_PROC_OUTPUT as table ( "ID" varchar(100) );

-- ############# TEST_PROC_A    TEST_PROC_A_VIEW

CREATE PROCEDURE TEST_PROC_A (

  OUT outTable tt_TEST_PROC_OUTPUT

)

LANGUAGE SQLSCRIPT READS SQL DATA

WITH RESULT VIEW TEST_PROC_A_VIEW AS

BEGIN

  outTable = SELECT 'A' AS "ID" FROM DUMMY;

END;

-- ############# TEST_PROC_B    TEST_PROC_B_VIEW

CREATE PROCEDURE TEST_PROC_B (

  IN  inVal_B   VARCHAR(100),

  OUT outTable  tt_TEST_PROC_OUTPUT

)

LANGUAGE SQLSCRIPT READS SQL DATA

WITH RESULT VIEW TEST_PROC_B_VIEW AS

BEGIN

  outTable = SELECT :inVal_B AS "ID" FROM DUMMY;

END;

-- ############# TEST_PROC_C    TEST_PROC_C_VIEW

CREATE PROCEDURE TEST_PROC_C (

  IN  inVal_C   VARCHAR(100),

  OUT outTable  tt_TEST_PROC_OUTPUT

)

LANGUAGE SQLSCRIPT READS SQL DATA

WITH RESULT VIEW TEST_PROC_C_VIEW AS

BEGIN

  outTable = SELECT "ID"

                      FROM TEST_PROC_B_VIEW

             WITH PARAMETERS (

               'placeholder'=('$$inval_b$$',':inVal_C')

             );

END;

-- ############# usage as SELECT

SELECT "ID" FROM TEST_PROC_A_VIEW;

SELECT "ID" FROM TEST_PROC_B_VIEW

  WITH PARAMETERS ( 'placeholder'=('$$inval_b$$','B') );

SELECT "ID" FROM TEST_PROC_C_VIEW

  WITH PARAMETERS ( 'placeholder'=('$$inval_c$$','C') );

-- ############# usage as CALL

CALL TEST_PROC_A ( NULL );

CALL TEST_PROC_B ( 'B', NULL );

CALL TEST_PROC_C ( 'C', NULL );

former_member184768
Active Contributor
0 Kudos

Hi Michael,

Please change the line:

'placeholder'=('$$inval_b$$',':inVal_C')

to the revised syntax as

PLACEHOLDER."$$inval_b$$" => :inVal_C

Regards,

Ravi


Former Member
0 Kudos

thanks, but that won't validate.  this is the first variation i tried.

henrique_pinto
Active Contributor
0 Kudos

At least in older revisions, HANA didn't support cascading parameters in nested procedure calls.

I'm not sure that's still the case. in rev60+.

Best,

Henrique.

PS: avoid replying in older closed threads, open a new thread for your question.

Former Member
0 Kudos

Hi, Ravindra:

Do you have any syntax for the input parameter mapping from one calculation view to another?

Best Regards,

Haifeng

former_member184768
Active Contributor
0 Kudos

Hi Haifeng,

Can you please open a new thread. This thread is already closed.

Regards,

Ravi