Subject | How do you write a procedure which returns a DataSet containing multiple tables |
---|---|
Author | Dixon |
Post date | 2010-03-22T18:54:23Z |
How do you write a procedure which returns a DataSet containing multiple tables
Below is a shortened version of what I've been trying to do. The first table in the dataset should have one record. the second table will have multiple records. However instead of getting a dataset with multiple tables, I'm getting one table with multiple rows.
SET TERM ^ ;
ALTER PROCEDURE USP_GETPERSONBYID (
VPERSONID Integer )
RETURNS (
OFIRSTNAME Varchar(50),
OMIDINIT Char(1),
OLASTNAME Varchar(50),
OACOMMID Integer,
OACOMMTYPEID Integer,
OACOMMTYPE Varchar(100),
OACOMMVAL Varchar(100),
OAISACTIVE Smallint,
AS
BEGIN
SELECT p1.FIRSTNAME , p1.MIDINIT , p1.LASTNAME
FROM mrPerson p1 WHERE p1.PersonId = :vPersonId
INTO :oFIRSTNAME , :oMIDINIT , :oLASTNAME
SUSPEND;
/* address communication*/
FOR
SELECT ac.COMMID, ac.COMMTYPEID, d.STRVAL, ac.COMMVAL, ac.ISACTIVE
FROM mrPerson p5
JOIN mrFamily m ON (p5.FAMILYID = m.FAMILYID)
JOIN AddressCommLinkup acl ON (m.ADDRESSID = acl.ADDRID)
JOIN mrComm ac ON (acl.COMMID = ac.COMMID)
JOIN mrDomain d ON (d.ID = ac.COMMTYPEID)
WHERE p5.PERSONID = :vPersonId
INTO :oaCOMMID, :oaCOMMTYPEID, :oaCOMMTYPE, :oaCOMMVAL , :oaISACTIVE
DO
SUSPEND;
END^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE USP_GETPERSONBYID TO MEMBERROSTER;
Below is a shortened version of what I've been trying to do. The first table in the dataset should have one record. the second table will have multiple records. However instead of getting a dataset with multiple tables, I'm getting one table with multiple rows.
SET TERM ^ ;
ALTER PROCEDURE USP_GETPERSONBYID (
VPERSONID Integer )
RETURNS (
OFIRSTNAME Varchar(50),
OMIDINIT Char(1),
OLASTNAME Varchar(50),
OACOMMID Integer,
OACOMMTYPEID Integer,
OACOMMTYPE Varchar(100),
OACOMMVAL Varchar(100),
OAISACTIVE Smallint,
AS
BEGIN
SELECT p1.FIRSTNAME , p1.MIDINIT , p1.LASTNAME
FROM mrPerson p1 WHERE p1.PersonId = :vPersonId
INTO :oFIRSTNAME , :oMIDINIT , :oLASTNAME
SUSPEND;
/* address communication*/
FOR
SELECT ac.COMMID, ac.COMMTYPEID, d.STRVAL, ac.COMMVAL, ac.ISACTIVE
FROM mrPerson p5
JOIN mrFamily m ON (p5.FAMILYID = m.FAMILYID)
JOIN AddressCommLinkup acl ON (m.ADDRESSID = acl.ADDRID)
JOIN mrComm ac ON (acl.COMMID = ac.COMMID)
JOIN mrDomain d ON (d.ID = ac.COMMTYPEID)
WHERE p5.PERSONID = :vPersonId
INTO :oaCOMMID, :oaCOMMTYPEID, :oaCOMMTYPE, :oaCOMMVAL , :oaISACTIVE
DO
SUSPEND;
END^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE USP_GETPERSONBYID TO MEMBERROSTER;