pears.NetCandidateSaveQuestions

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetCandidateSaveQuestions"( IN "pWebUserID" CHAR(20),IN "qanswers" long VARCHAR DEFAULT NULL,IN "pExperience" long VARCHAR DEFAULT NULL,IN "pSourceID" CHAR(20) DEFAULT NULL,IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "qOriginID" CHAR(20);
  DECLARE "CombinedNote" long VARCHAR;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '97:~Unique candidate link required';
    RETURN
  END IF;
  IF "isnull"("pSourceID",'') <> '' THEN
    SET "qOriginID" = "isnull"("pSourceID",(SELECT top 1 "originid" FROM "origin" WHERE "descrip" IN( 'none','unknown' ) ORDER BY "sortorder" ASC));
    UPDATE "person" SET "originid" = "qOriginID" WHERE "personid" = "pPersonID"
  END IF;
  IF "isnull"("pExperience",'none') <> 'none' THEN
    SET "CombinedNote" = "string"('What is your experience: ',"pExperience","char"(13),"char"(13),(SELECT top 1 "notes" FROM "person" WHERE "personid" = "pPersonID"));
    UPDATE "person" SET "notes" = "CombinedNote" WHERE "personid" = "pPersonID"
  END IF;
  IF "isnull"("qanswers",'none') <> 'none' THEN
    CALL "IQXNetSaveQuestionnaire"("pPersonID","qanswers")
  END IF;
  SELECT '0:~Success'
END /* DOC
2017-11-07 GJ Created - Probably needs tweaking
2017-11-24 PC Needed tweaking :slightly_smiling_face:
2017-11-27 PC modified for Experience and Source fields
2018-02-02 PC spelling change
2018-07-19 GJ Changed to not change the sourceID when this procedure is called for the 2nd time
2018-10-22 MHS IW-456 multi-cand fix
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Inputs:
pWebUserID
qanswers
 
Outputs:
pResult
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateSaveQuestions" IS 
{CREATE PROCEDURE pears."NetCandidateSaveQuestions"( IN pWebUserID CHAR(20),IN qanswers long VARCHAR DEFAULT NULL,IN pExperience long VARCHAR DEFAULT NULL,IN pSourceID CHAR(20) DEFAULT NULL, IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( pResult CHAR(250) ) 
// IQXWeb
BEGIN
    DECLARE qOriginID CHAR(20);
    DECLARE CombinedNote long VARCHAR;
 
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    IF pPersonID IS NULL THEN
       SELECT '97:~Unique candidate link required';
       RETURN;
    END IF;
 
    IF isnull(pSourceID,'') != '' THEN
        SET qOriginID = isnull(pSourceID,(SELECT top 1 originid FROM origin WHERE descrip IN ('none','unknown') ORDER BY sortorder));
        UPDATE person SET originid=qOriginID WHERE personid=pPersonID;
    END IF;
 
    IF isnull(pExperience,'none') != 'none' THEN
        SET CombinedNote = string('What is your experience: ',pExperience,CHAR(13),CHAR(13),(SELECT top 1 notes FROM person WHERE personid=pPersonID));
        UPDATE person SET notes=CombinedNote WHERE personid=pPersonID;
    END IF;
 
 
   IF isnull(qanswers,'none') != 'none' THEN
        CALL "IQXNetSaveQuestionnaire"(pPersonID,"qanswers");
    END IF;
    SELECT '0:~Success';
END
 
/* DOC
2017-11-07 GJ Created - Probably needs tweaking
2017-11-24 PC Needed tweaking :slightly_smiling_face:
2017-11-27 PC modified for Experience and Source fields
2018-02-02 PC spelling change
2018-07-19 GJ Changed to not change the sourceID when this procedure is called for the 2nd time
2018-10-22 MHS IW-456 multi-cand fix
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Inputs:
    pWebUserID
    qanswers
 
Outputs:
    pResult
 
*/
}