Parallel Processing and Parameters

Return to your procedure called get_po_header_data.

Define two tabular output parameters which will be used to explicitly pass the results of the SELECT statements to the caller.

Next, assign SELECT statements to the output parameters as shown here.

The completed code should be similar to this.

PROCEDURE "get_po_header_data"(
         OUT EX_PO_CREATE_CNT TABLE(
           CREATE_CNT INTEGER,
           "CREATEDBY" NVARCHAR(255)),
        OUT EX_PO_CHANGE_CNT TABLE(
           CHANGE_CNT INTEGER,
           "MODIFIEDBY" NVARCHAR(255))  )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
BEGIN

ex_po_create_cnt =  SELECT COUNT(*) AS CREATE_CNT, "CREATEDBY"
     FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
            GROUP BY  "CREATEDBY";

ex_po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY"
     FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
             GROUP BY  "MODIFIEDBY";

END