How to call procedures in SQLscript

[Former Member]

Hi,

I have a stored procedure as follows

PROCEDURE "schema"."myProc" ()
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
  DEFAULT SCHEMA "schema"
  AS
BEGIN
SELECT * FROM "TABLE";
END;

This procedure on call from a SQL editor i.e., call "schema"."myProc"() would result into a records from the table "TABLE".

How can I call the same procedure from another procedure and assign the records from "schema"."myProc"() into a intermediate variable and manipulate the result.

If I use "WITH RESULT VIEW myView" option in a .hbdprocedure, editor throws an error saying "The With Result View statement is not permitted".

Regards,

Vijay.

HowmyProc" ()

[Former Member · Accepted solution]

Hi Vijay,

I think you can use the table type and OUT parameter to solve this problem. And fill in the intermediate variable you want. Something like the following code

PROCEDURE "schema"."myProc" (OUT out TT_TABLE)  
  LANGUAGE SQLSCRIPT  
  SQL SECURITY INVOKER  
  DEFAULT SCHEMA "schema"  
  AS  
BEGIN  
out = SELECT * FROM "TABLE";  
END;  

Then you can use the following CALL statement in another precedure to assign the result to the "out" variable.

CALL "schema"."myProc"(out);

Best regards,

Wenjun

[Former Member · Accepted solution]

Hi Zhou,

Thanks for quick reply, yes this method is one way to achieve the result

The problem with using a "OUT out TT_TABLE" as a output parameter (in my case) is I dont know the table structure of the output, TT_TABLE structure is determined dynamically depending on few conditions in the procedure.So I cannot pre-define the table type for the output.

I was just wondering if we can assign the output table of a  procedure call to some intermediate varibale as in below code

temp_table =call "schema"."myproc"();

But this is not allowed, I am getting an error for this. Am I missing some syntax or this kind of a operation is not allowed in SQLScript? .

In xsjs I am able to iterate throught the result set of "myproc"() so I am assuming there should be something in SQLScript to achieve this kind of operation.

Any input would be much appreciated.

Regards,

Vijay.

[former_member182302 · Accepted solution]

Hi Vijay,

You cannot CALL READ-WRITE procedures from calculation views. You can only call a READ ONLY procedure.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

"schema"."myproc"() is a read only procedure, in that case how can operate on the output?

Regards,
Vijay.

[former_member182302 · Accepted solution]

Hi Vijay,

It is a read only procedure but the return type is not Static . Not sure if you can capture the output in that case.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

I am able to capture the output in xsjs, but not finding anything in SQLScript, Thanks for the response .

Regards,

Vijay.

[former_member182302 · Accepted solution]

Yes Vijay . On the service we can do that, even for the READ-WRITE procedures too.

Regards,

Krishna Tangudu