Skip to Content
avatar image
Former Member

How to call procedures in SQLscript

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" ()

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 13, 2015 at 02:36 AM

    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

    Add comment
    10|10000 characters needed characters exceeded