pears.SetQuestMultiSelection

CREATE PROCEDURE "pears"."SetQuestMultiSelection"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "stagloc" CHAR(3),IN "stagid" CHAR(3),IN "sparentid" CHAR(20),IN "svalue" long VARCHAR ) 
BEGIN
  DECLARE "i" INTEGER;
  DECLARE "stagchoiceid" CHAR(4);
  DECLARE "x" CHAR(4);
  DECLARE "schoicevalue" long VARCHAR;
  DECLARE "stagtype" CHAR(1);
  SELECT "tagtype" INTO "stagtype" FROM "tag" WHERE "tag"."taglocation" = "stagloc" AND "tag"."tagid" = "stagid";
  IF "charindex"("char"(10),"svalue") > 0 THEN -- Do not want to risk having line feeds
    SET "svalue" = "replace"("svalue","char"(10),'')
  END IF;
  SET "svalue" = "string"("svalue","char"(13),'   '); -- Ensure it has trailing CR and some extra space so that STUFF does not return emptry string and fall over
  loop
    SET "i" = "charindex"("char"(9),"svalue");
    IF "i" = 0 THEN
      RETURN
    END IF;
    SET "stagchoiceid" = "trim"("left"("svalue","i"-1));
    SET "svalue" = "stuff"("svalue",1,"i",'');
    SET "i" = "charindex"("char"(13),"svalue");
    SET "schoicevalue" = "left"("svalue","i"-1);
    SET "svalue" = "stuff"("svalue",1,"i",'');
    IF "stagchoiceid" <> '' THEN
      IF "stagtype" <> 'Q' THEN
        CALL "setquestanswer"("stagloc","stagid","stagchoiceid","sparentid","schoicevalue")
      ELSE -- Submenu question
        SELECT "tagchoiceparentid" INTO "x" FROM "tagchoice" WHERE "taglocation" = "stagloc" AND "tagid" = "stagid" AND "tagchoiceid" = "stagchoiceid";
        IF "x" IS NOT NULL THEN -- Format is as per GetQuestMultiSelection (rather than that used by IQX) so need to rearrange
          IF "schoicevalue" = '1' THEN
            SET "schoicevalue" = "stagchoiceid"
          ELSE
            SET "schoicevalue" = '0' -- Should be 0 already if not 1
          END IF;
          SET "stagchoiceid" = "x"
        END IF;
        CALL "setquestanswer"("stagloc","stagid","schoicevalue","sparentid","stagchoiceid")
      END IF
    END IF
  END loop
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."SetQuestMultiSelection" IS 
{CREATE PROCEDURE SetQuestMultiSelection 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN stagloc CHAR(3),IN stagid CHAR(3),IN sparentid CHAR(20),IN svalue long VARCHAR)
BEGIN
  DECLARE i INTEGER;
  DECLARE stagchoiceid CHAR(4);
  DECLARE x CHAR(4);
  DECLARE schoicevalue long VARCHAR;
  DECLARE stagtype CHAR(1);
  SELECT tagtype INTO stagtype FROM tag WHERE tag.taglocation=stagloc AND tag.tagid=stagid;
  IF charindex("char"(10),svalue) > 0 THEN -- Do not want to risk having line feeds
    SET svalue=REPLACE(svalue,"char"(10),'')
  END IF;
  SET svalue=string(svalue,"char"(13),'   '); -- Ensure it has trailing CR and some extra space so that STUFF does not return emptry string and fall over
  loop
    SET i=charindex("char"(9),svalue);
    IF i = 0 THEN
      RETURN
    END IF;
    SET stagchoiceid=TRIM("left"(svalue,i-1));
    SET svalue=stuff(svalue,1,i,'');
    SET i=charindex("char"(13),svalue);
    SET schoicevalue="left"(svalue,i-1);
    SET svalue=stuff(svalue,1,i,'');
    IF stagchoiceid <> '' THEN
        IF stagtype<>'Q' THEN 
           CALL setquestanswer(stagloc,stagid,stagchoiceid,sparentid,schoicevalue)
        ELSE -- Submenu question
            SELECT tagchoiceparentid INTO x FROM tagchoice WHERE taglocation=stagloc AND tagid=stagid AND tagchoiceid=stagchoiceid;
            IF x IS NOT NULL THEN -- Format is as per GetQuestMultiSelection (rather than that used by IQX) so need to rearrange
                IF schoicevalue='1' THEN
                    SET schoicevalue=stagchoiceid
                ELSE 
                    SET schoicevalue='0'  -- Should be 0 already if not 1
                END IF; 
                SET stagchoiceid=x;
            END IF;
            CALL setquestanswer(stagloc,stagid,schoicevalue,sparentid,stagchoiceid) 
        END IF
    END IF
  END loop
END
}