Skip to Content

Intermediate Table Variables

Leverage SQLScript in stored procedures, user defined functions, and user defined libraries.
You will learn

In this exercise you will modify the code of procedure get_po_header_data again to use a single tabular output. Existing queries will be reused based on intermediate table variables.

rich-heilmanRich HeilmanJanuary 24, 2022
Created by
rich-heilman
February 11, 2021
Contributors
rich-heilman

Prerequisites

  • Step 1
    1. Return to your procedure called get_po_header_data.

      Existing Procedure
    2. Delete the output parameters which you defined in the last section.

      Define output
    3. Define a new output parameter as shown

      New output
    4. Rename EX_PO_CREATE_CNT to PO_CREATE_CNT. Also rename EX_PO_CHANGE_CNT to PO_CHANGE_CNT

      change name
    5. Modify the two SELECT statements and add AS EID after the CREADEDBY and MODIFIEDBY fields.

      modify select
    6. Next, add another SELECT statement after the 2 previous SELECT statements as shown. This statement uses the previously defined table variables.

      add another select
    7. The completed code should be very similar to this.

      SQLCRIPT
      Copy
      PROCEDURE "get_po_header_data"(
                OUT EX_TOP_3_EMP_PO_COMBINED_CNT TABLE(
                    LOGINNAME NVARCHAR(12),
                    CREATE_CNT INTEGER,
                    CHANGE_CNT INTEGER,
                    COMBINED_CNT INTEGER )  )
         LANGUAGE SQLSCRIPT
         SQL SECURITY INVOKER
         --DEFAULT SCHEMA <default_schema_name>
         READS SQL DATA AS
      BEGIN
      
      po_create_cnt =  SELECT COUNT(*) AS CREATE_CNT, "CREATEDBY" as EID
           FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                  GROUP BY  "CREATEDBY";
      
      po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY" as EID
           FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                   GROUP BY  "MODIFIEDBY";
      
      EX_TOP_3_EMP_PO_COMBINED_CNT =
              SELECT emp.LOGINNAME, crcnt.CREATE_CNT, chcnt.CHANGE_CNT,  crcnt.CREATE_CNT +
              chcnt.CHANGE_CNT AS COMBINED_CNT
                  FROM "OPENSAP_MD_EMPLOYEES" as emp
                  LEFT OUTER JOIN :PO_CREATE_CNT AS crcnt
                   ON emp.email = crcnt.EID
                 LEFT OUTER JOIN :PO_CHANGE_CNT AS chcnt
                 ON emp.email = chcnt.EID
                ORDER BY COMBINED_CNT DESC LIMIT 3;
      
      END
      
      
  • Step 2
    1. Save the procedure.

      save Procedure
    2. Perform a Deploy.

      deploy
    3. Return to the Database Explorer page and use what you have learned and generate a new call statement for the procedure and run it. The results are then shown.

      Results
Back to top