Create SQLScript User Defined Libraries

Click on the new masterdata.hdblibrary file in the libraries folder and the editor should open with the shell of the library.

Between the BEGIN and END statements, define a public variable library member called rowcount with type int.

public variable rowcount int;

After the public variable, define a public function library member as shown here.
```SQLCRIPT
public function “employee_exists”(
in im_employee_id nvarchar(36) )
returns res boolean as
begin
declare lv_count int;
select count(*) into lv_count from “OPENSAP_MD_EMPLOYEES”
where id = im_employee_id;
if lv_count > 0 then
res = true;
else
res = false;
end if;
end;

```

After the public function, define two public procedure library members, one called get_employee_data and the other
get_business_partner_data.

public procedure "get_employee_data"(
             out ex_employees "OPENSAP_MD_EMPLOYEES" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
begin
  ex_employees = select * from "OPENSAP_MD_EMPLOYEES";
  rowcount = record_count(:ex_employees);
end;

public procedure "get_business_partner_data"(
             out ex_businesspartners "OPENSAP_MD_BUSINESSPARTNERS" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
    AS
begin
  ex_businesspartners = select * from "OPENSAP_MD_BUSINESSPARTNERS";
  rowcount = record_count(:ex_businesspartners);
end;

The complete code should look like this.

LIBRARY "masterdata"
LANGUAGE SQLSCRIPT
AS
BEGIN

public variable rowcount int;

public function "employee_exists"(
             in im_employee_id nvarchar(36) )
             returns res boolean as
begin
    declare lv_count int;
    select count(*) into lv_count from "OPENSAP_MD_EMPLOYEES"
                           where id = im_employee_id;
    if  lv_count > 0 then
     res = true;
    else
     res = false;
    end if;
end;

public procedure "get_employee_data"(
             out ex_employees "OPENSAP_MD_EMPLOYEES" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
begin
  ex_employees = select * from "OPENSAP_MD_EMPLOYEES";
  rowcount = record_count(:ex_employees);
end;

public procedure "get_business_partner_data"(
             out ex_businesspartners "OPENSAP_MD_BUSINESSPARTNERS" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
    AS
begin
  ex_businesspartners = select * from "OPENSAP_MD_BUSINESSPARTNERS";
  rowcount = record_count(:ex_businesspartners);
end;

END

Save your work.

Use what you have learned and create a new procedure in the procedures folder called get_master_data. Because our library procedures actually update the library variable, we need our consuming procedure to be Read/Write as well, so make sure to remove the READS SQL DATA from this procedure as shown.

Enter the output parameters in the signature of the procedure.

out ex_emp_exists boolean,
out ex_employees "OPENSAP_MD_EMPLOYEES" ,
out ex_emp_count int,
out ex_business_partners "OPENSAP_MD_BUSINESSPARTNERS",
out ex_bp_count int

ex_emp_exists  = "masterdata":"employee_exists"('02BD2137-0890-1EEA-A6C2-BB55C19787FB');   

call "masterdata":"get_employee_data"( ex_employees );
ex_emp_count = "masterdata":rowcount;

call "masterdata":"get_business_partner_data"( ex_business_partners );
ex_bp_count = "masterdata":rowcount;

The completed code should look similar to this.

PROCEDURE "get_master_data"(
      out ex_emp_exists boolean,
      out ex_employees "OPENSAP_MD_EMPLOYEES" ,
      out ex_emp_count int,
      out ex_business_partners "OPENSAP_MD_BUSINESSPARTNERS",
      out ex_bp_count int
 )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   AS
BEGIN

ex_emp_exists  = "masterdata":"employee_exists"('02BD2137-0890-1EEA-A6C2-BB55C19787FB');   

call "masterdata":"get_employee_data"( ex_employees );
ex_emp_count = "masterdata":rowcount;

call "masterdata":"get_business_partner_data"( ex_business_partners );
ex_bp_count = "masterdata":rowcount;

END