pears.DashboardStaffDivisions

CREATE PROCEDURE "pears"."DashboardStaffDivisions"()
RESULT( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  "DivisionID" CHAR(20),"Name" CHAR(100) ) 
BEGIN
  DECLARE "pStaffID" CHAR(20);
  DECLARE "pDivAccess" SMALLINT;
  DECLARE "pOwnDivID" CHAR(20);
  SELECT FIRST "staffid","divisionaccess","divisionid" INTO "pStaffID","pDivAccess","pOwnDivID" FROM "staff" WHERE "staffid" = "userstaffid";
  CASE "pDivAccess"
  WHEN 1 THEN
    SELECT "divisionid","name" FROM "division" WHERE "divisionid" = "pOwnDivID" UNION SELECT '','No Division'
  WHEN 2 THEN
    SELECT "a"."divisionid","d"."name" FROM "divisionaccess" AS "a" KEY JOIN "division" AS "d" WHERE "a"."staffid" = "pStaffID" UNION SELECT '','No Division'
  ELSE
    SELECT "divisionid","name" FROM "division" UNION SELECT '','No Division'
  END CASE
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DashboardStaffDivisions" IS 
{CREATE PROCEDURE DashboardStaffDivisions 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
()
RESULT( DivisionID CHAR(20),Name CHAR(100) ) 
BEGIN
  DECLARE pStaffID CHAR(20);
  DECLARE pDivAccess SMALLINT;
  DECLARE pOwnDivID CHAR(20);
  SELECT FIRST staffid,divisionaccess,divisionid INTO pStaffID,pDivAccess,pOwnDivID FROM staff WHERE staffid = userstaffid;
  CASE pDivAccess
  WHEN 1 THEN
    SELECT divisionid,name FROM division WHERE divisionid = pOwnDivID UNION SELECT '','No Division'
  WHEN 2 THEN
    SELECT a.divisionid,d.name FROM divisionaccess AS a KEY JOIN division AS d WHERE a.staffid = pStaffID UNION SELECT '','No Division'
  ELSE
    SELECT divisionid,name FROM division UNION SELECT '','No Division'
  END CASE
END
}