pears.NetOwnerAggregateSearch

CREATE PROCEDURE "pears"."NetOwnerAggregateSearch"( IN "pWebUserID" CHAR(20),IN "SearchValue" CHAR(20) DEFAULT '%',IN "SearchCategory" CHAR(5) DEFAULT NULL,IN "pSlice" INTEGER DEFAULT 1,IN "pSliceSize" INTEGER DEFAULT 20 ) 
RESULT( "ObjectID" CHAR(20),"ObjectName" CHAR(60),"Town" CHAR(100),"JobTitle" CHAR(60),"Status" CHAR(10),"companyName" CHAR(60),"Alert" CHAR(255),"IsFavourite" INTEGER ) 
BEGIN
  IF "length"("isnull"("SearchValue",'')) = 0 THEN
    SELECT '~','','','','','','',0;
    RETURN
  END IF;
  SELECT "Employmentid","name","address","jobtitle",'Contact' AS "status","companyname","alert","isfavourite" FROM "pears"."NetOwnerContactSearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize") UNION
  SELECT "companyid","name","address",'' AS "Jobtitle",'Company' AS "status",'',"alert","isfavourite" FROM "pears"."NetOwnerCompanySearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize") UNION
  SELECT "personid","name","address",'' AS "Jobtitle",'Candidate' AS "status",'',"alert","isfavourite" FROM "pears"."NetOwnerCandidateSearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize")
    ORDER BY "name" ASC
END /* DOC
2016-09-19 PC alter routine to allow search, and paging
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAggregateSearch" IS 
{CREATE PROCEDURE pears."NetOwnerAggregateSearch"(IN "pWebUserID" CHAR(20),IN "SearchValue" CHAR(20) DEFAULT '%',IN SearchCategory CHAR(5) DEFAULT NULL,IN pSlice INTEGER DEFAULT 1, IN pSliceSize INTEGER DEFAULT 20 )
RESULT(ObjectID CHAR(20),ObjectName CHAR(60),Town CHAR(100),JobTitle CHAR(60),STATUS CHAR(10),companyName CHAR(60),Alert CHAR(255),IsFavourite INTEGER )
BEGIN
    IF LENGTH(isnull(SearchValue,''))=0 THEN
        SELECT '~','','','','','','',0;
        RETURN;
    END IF;
   SELECT Employmentid,name,address,jobtitle,'Contact' AS STATUS,companyname,alert,isfavourite FROM "pears"."NetOwnerContactSearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   UNION
   SELECT companyid,name,address,'' AS Jobtitle,'Company' AS STATUS,'',alert,isfavourite FROM  "pears"."NetOwnerCompanySearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   UNION
   SELECT personid,name,address,'' AS Jobtitle,'Candidate' AS STATUS,'',alert,isfavourite FROM "pears"."NetOwnerCandidateSearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   ORDER BY name ASC
END
 
/* DOC
2016-09-19 PC alter routine to allow search, and paging
 
*/
}