How to call ProcedureView with parameters

[acaireta]

Hello,

I'm trying to create a stored procedure that calls a StoredProcedure-view.

Question 1:

They can explain to me how I can call StoredProcedure-view using variables?

EXAMPLE:

--this works
          EXEC 'INSERT INTO #TMP_GSP__MNOITT
          SELECT * FROM GSP_PROC_MNOITT_VIEW 
          WITH PARAMETERS(
          ''placeholder'' = (''$$itemcode$$'',''' || :itemcode || '''),
          ''PLACEHOLDER'' = (''$$_level$$'',''0''),
          ''PLACEHOLDER'' = (''$$parent$$'',''-1''),
          ''PLACEHOLDER'' = (''$$parentqty$$'',''0''))';
--this NOT works
          INSERT INTO #TMP_GSP_RECURS_MNOITT
          SELECT * FROM GSP_PROC_MNOITT_VIEW 
          WITH PARAMETERS(
          PLACEHOLDER."$$itemcode$$" => :itemcode ,
          PLACEHOLDER."$$parent$$" => :parent,
          PLACEHOLDER."$$parentqty$$" => :parentqty);

Question 2:

I can pass a NULL value to a nvarchar parameter?

In this example the parameter 'level' is integer and NULL received correctly, however the parameter 'parent' is nvarchar (5000) and receives an alphanumeric value 'null'.

SELECT * FROM GSP_PROC_MNOITT_VIEW WITH PARAMETERS(
'PLACEHOLDER' = ('$$itemcode$$', '02005300186401002116'),
'PLACEHOLDER' = ('$$_level$$', 'null'),
'PLACEHOLDER' = ('$$parent$$','null'),
'PLACEHOLDER' = ('$$parentqty$$','1'));
--this NOT works
SELECT * FROM GSP_PROC_MNOITT_VIEW WITH PARAMETERS(
'PLACEHOLDER' = ('$$itemcode$$', '02005300186401002116'),
'PLACEHOLDER' = ('$$_level$$', 'null'),
'PLACEHOLDER' = ('$$parent$$',null),
'PLACEHOLDER' = ('$$parentqty$$','1'));

I'm working with HANA PLATFORM Rev. 53.

Thank you very much.

[Former Member · Accepted solution]

You should judge the null value in the procedure.

insert into ldl_test values (1, 'abc');

insert into ldl_test values (2, 'xyz');

insert into ldl_test values (3, null);

create procedure proc_test(p1 integer, p2 nvarchar(100), out o1 ldl_test)

  LANGUAGE SQLSCRIPT

  READS SQL DATA

  WITH RESULT VIEW ProcView

AS

begin

  if p2 != 'null' then -- the 'null' parameter

    o1 = SELECT * FROM ldl_test where x = :p1 and y = :p2;

  else

    o1 = SELECT * FROM ldl_test where x = :p1 and y is null;

  end if;

end;

--query the common value

select * from ProcView WITH PARAMETERS('placeholder' = ('$$p1$$', '1'), 'placeholder' = ('$$p2$$', 'abc'))

--query the null value

select * from ProcView WITH PARAMETERS('placeholder' = ('$$p1$$', '3'), 'placeholder' = ('$$p2$$', 'null'))

[acaireta · Accepted solution]

Thank you very much for your comments.

Indeed the only way is to define an alphanumeric value to replace null.

On the other hand, knows how to call a StoredProcedure-view using variables?  (This is the question 1 of this discussion)

Thanks very much.

[Trinidad · Accepted solution]

Hi Adria,

--query the common value

select

* from ProcView WITH PARAMETERS('placeholder' = ('$$p1$$', '1'), 'placeholder' = ('$$p2$$', 'abc'))

You already tried it?

Your line with EXEC and the one wihout are quite different. In the case the sample here bellow doesn't answer your problem please share a sample where the EXEC line and the direct one are equal.

Regards,

Trinidad.

[acaireta · Accepted solution]

Hello,

I attach a simple example to understand my problem.

Thank you very much.

DROP TYPE TYPE_TEST;
CREATE TYPE TYPE_TEST AS TABLE ("ItemName" nvarchar(100));
DROP PROCEDURE TEST_CALL;
CREATE PROCEDURE TEST_CALL(in ItemCode nvarchar(20), out t_out TYPE_TEST) 
LANGUAGE SQLSCRIPT  READS SQL DATA WITH RESULT VIEW TEST_CALL_VIEW AS
BEGIN
          t_out = SELECT "ItemName" FROM OITM WHERE "ItemCode" = :ItemCode;
END;
DROP PROCEDURE TEST_CALL0;
CREATE PROCEDURE TEST_CALL0 (in ItemCode nvarchar(20))
LANGUAGE SQLSCRIPT AS
BEGIN
          --THIS WORKS
          EXEC 'select * from TEST_CALL_VIEW WITH PARAMETERS(''placeholder'' = (''$$itemcode$$'', ''' || ItemCode || '''))';
          --THIS NOT WORKS
          --select * from TEST_CALL_VIEW WITH PARAMETERS(PLACEHOLDER."$$itemcode$$" => :itemcode);
END;
CALL TEST_CALL0('.');

[acaireta · Accepted solution]

Hello,

I found the solution to my incidence, first syntax was wrong this is the correct Syntax:

          --THIS NOT WORKS 

          --select * from TEST_CALL_VIEW WITH PARAMETERS(PLACEHOLDER."$$itemcode$$" => :itemcode);

                      --THIS WORKS

          select * from TEST_CALL_VIEW (PLACEHOLDER."$$itemcode$$" => :itemcode); 

I found the information in this discussion: http://scn.sap.com/message/14392669

Thanks very much.

[rama_shankar3]

Adria:

Try adding a double quote for table name:  i.e: "#TMP_GSP_RECURS_MNOITT".

On the null value - yes you can pass a null value to the column with data type nvarchar .

hope this helps.

Regarsds,

Rama

[acaireta]

Hello,

Rama Shankar wrote:Try adding a double quote for table name:  i.e: "#TMP_GSP_RECURS_MNOITT".

I'll return the following error (view last reply plis)

Rama Shankar wrote:On the null value - yes you can pass a null value to the column with data type nvarchar .

I appreciate your response, but I have specified that way I can pass a null value to a parameter nvarchar.

I test the following:

 SELECT * FROM GSP_PROC_MNOITT_ITT1_VIEW 
          WITH PARAMETERS(
          'placeholder' = ('$$returnalloittversions$$','N'),
          'placeholder' = ('$$itemcode$$','02005300186401002188'),
          'PLACEHOLDER' = ('$$_level$$','0'),
          'PLACEHOLDER' = ('$$parent$$',null),
          'PLACEHOLDER' = ('$$parentqty$$','0'))

I'll return the following error:

Could not execute 'SELECT * FROM GSP_PROC_MNOITT_ITT1_VIEW WITH PARAMETERS( 'placeholder' = ...' in 1 ms 949 µs .

SAP DBTech JDBC: [257] (at 238): sql syntax error: incorrect syntax near "null": line 6 col 32 (at pos 238)

That way I can define a nvarchar parameter as NULL?

Thanks very much.

[Former Member]

Hi Adria,

What happens if you don't specify the placeholder for $$parent$$' instead try to pass null?

Best regards

[acaireta]

Hi,

If I do not specify parameter '$$parent$$' this is the error message:

Could not execute 'SELECT * FROM GSP_PROC_MNOITT_ITT1_VIEW WITH PARAMETERS( 'placeholder' = ...' in 2 ms 147 µs .

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

Thanks.

[Former Member]

You should be able to set a default of null for $$parent$$ in the view.

However, combining views and passing parameters within SQLScript is rather complex. Why not just write the whole model using SQLScript? That would seem to be a lot easier.

John

[Former Member]

Hi,

Which  the error messages are you getting?

Best regards

[acaireta]

Hi,

The error message is: SAP DBTech JDBC: [257] (at 2678): sql syntax error: incorrect syntax near ".": line 73 col 2 (at pos 2678)

Hi inserted a new parameter into storedprocedure call.

Isaías Cristiano Barroso wrote:
Hi,
 Which  the error messages are you getting?
Best regards