Union in stored proc with cursor

Create Procedure wsp_GetTabs
 
(
  DCode Varchar(10),
  UserLogin VARCHAR(10)
 
 
 
)
 
 
  RESULT SETS 1
  LANGUAGE SQL
  Not Deterministic
 
BEGIN
 
 
   --DECLARE SQLCODE INTEGER DEFAULT 0;
  -- DECLARE retCode INTEGER DEFAULT 0;
 
  --DECLARE SQLStmtSelect VARCHAR(8000);
  --DECLARE c1  SCROLL CURSOR WITH RETURN FOR s1; 
 
 
 
 
 
 -- gets tab order. Changing "tab" to treeview. Some of the nodes have subnodes. Mark them with X
 -- Code will loop thru and display the subnode.
 
--Bankruptcy is tab 08
--Account Status --> 02	
--Account Maint. --> 03	
--Joint/IB -->11 : has 2 subnodes: one for account , one for joint borrower name
--Charge Off --> 14	
-- User Def. Acct -->17	
-- Account Detail --> 19	
-- Repo --> 21	
-- Special Assets -->22	
-- NSF Entry --> 23	
--Enter Legal Data-->30--Payment --> 04
 
   DECLARE c1 CURSOR  WITH HOLD WITH RETURN FOR  select 'DBDetail' as MainNode ,
       '99' as NodeID,
        '*' as HasSubNode,
        '*' as HasData,
        d2ref as SubNode,
        d2snam as SubSubNode from dbdetail where d2dcode = DCode
 
    Union
 
 
(SELECT
 
 
  T1CAPT, 
        t1id2 as NodeID ,  
        case when t1id2 in ('04','11','14','02','17','19','08','03','21','22','23','30','24')
                         then 
                             case when t1id2='11' then 'XX'
                              else 'X' end
                 
        else '*' end,  
 
       (CASE WHEN U.Dcode1 IS NULL 
             THEN IFNULL(tl.tablename,'*')
             ELSE 'Data'
             END ), 
       IFNULL(account,'*'), 
       IFNULL(rtrim(IBName),'*')  
 
 
FROM wqTabTables AS TL 
LEFT JOIN ( 
     SELECT E1DCOD as Dcode1, 'EMPLRP' AS tName, '07' as TabID  ,'' as Account , '' as IBName From EMPLRP WHERE E1DCOD=DCode  --employer
     UNION 
     SELECT C1DCOD as Dcode1, 'COMKRPF','06' ,'' ,''  FROM COMKRPF WHERE C1DCOD=DCode and C1Ref = '' --contacts
     UNION 
     SELECT C1DCOD as Dcode1, 'COMKRPF','11' ,'',''  FROM COMKRPF   where c1dcod=DCode  and  not (C1Ref) = '' --co-maker/joint IB
     UNION 
     SELECT S9DEBT as Dcode1, 'DBMSUPP1' ,'18' ,'','' FROM DBMSUPP1 WHERE S9DEBT=DCode --supplemental info
     UNION
     SELECT ANDCOD as Dcode1, 'AN0010PF', '10' ,'','' FROM AN0010PF WHERE ANDCOD=DCode --alert notes
     UNION
     SELECT BKDCODE as Dcode1, 'BANKOMST','08','','' FROM BANKOMST WHERE BKDCODE=DCode --bankruptcy
     UNION
     SELECT CHGDCOD as Dcode1, 'CHG060PF','14','','' FROM CHG060PF WHERE CHGDCOD=DCode --chargeoff
     UNION
     SELECT A1DCOD as Dcode1, 'DBACTION' ,'09','','' FROM DBACTION WHERE A1DCOD=DCode --call back
     UNION
     SELECT DSDCOD as Dcode1, 'DBMSUPPA' ,'05','','' FROM DBMSUPPA WHERE DSDCOD=DCode --mailing address
     UNION
     SELECT PNDCOD as Dcode1, 'PAYNOTES' ,'15','','' FROM PAYNOTES WHERE PNDCOD=DCode --pay alert notes
     UNION
     SELECT R5DCOD as Dcode1, 'REPORPF' ,'21','','' FROM REPORPF WHERE R5DCOD=DCode --reposs
     UNION
     SELECT XDDCOD as Dcode1, 'STXDSDB' ,'29','','' FROM STXDSDB WHERE XDDCOD=DCode -- skip trace
     UNION
     SELECT UMDBTCDE as Dcode1, 'UDMST' ,'16' ,'','' FROM UDMST WHERE UMDBTCDE=DCode --user defined mst
     UNION  /* take this out per dan on 01/17/08 but then I put it back per meeting on 01/18/2008. Check for status, if not blank, then make red */
    SELECT D2DCODE as Dcode1, 'DBDETAIL' ,'02' ,'','' FROM DBDETAIL WHERE D2DCODE=DCode and exists (select * from dbdetail where not (d2ref)  = '' and d2dcode=DCode) --account status
     UNION
     SELECT A3DCODA as Dcode1, 'AST001PF' ,'22','','' FROM AST001PF WHERE A3DCODA=DCode --special Asset
     UNION
     SELECT d6dcod as Dcode1, 'paysched','04','','' FROM paysched WHERE d6dcod=DCode --payment sched
     UNION
     SELECT NSFDCOD as Dcode1, 'NSF060PF','23','','' FROM NSF060PF WHERE NSFDCOD=DCode --NSF entry
     UNION
     SELECT CCDCOD as Dcode1, 'CREDCARD','12','','' FROM CREDCARD WHERE CCDCOD=DCode --credit card
     UNION
     SELECT DCDCOD as Dcode1, 'DIRCHK','13' ,'','' FROM DIRCHK WHERE DCDCOD=DCode --electronic check
     UNION
     SELECT UDDBTCDE as Dcode1, 'udmaster','17','','' FROM udmaster WHERE UDDBTCDE=DCode --user defined detail/account
     UNION
     SELECT I1Dcod as Dcode1, 'ININFO','24' ,'','' FROM ININFO WHERE I1Dcod=DCode --patient info
 
     ) AS U 
     ON TL.tableName = U.tName 
 
right join taborder on u.tabid= t1id2 
 
where T1USER =  UserLogin )
 
order by NodeID;
 
    
    /*   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING , NOT FOUND
     Begin
       SET retCode = SQLCODE;
       Insert into  VQUECTL.EVENTLOG (EVAGENCY,EVWHEN,EVWHENT,EVWHO,EvPGM,EVMSG,EVTYPE)
          values(Right(current schema,3),CURDATE(),CURTIME(),UserLogin,'wsp_GetTabs SP','Code (No Error if Code=0) :' || retcode ,'2');
     End; */
 
         -- Log SP
    --INSERT INTO  VQUECTL.EVENTLOG (EVWHEN,EVWHENT,EVWHO,EvPGM,EVMSG,EVTYPE)
      --  values(CURDATE(),CURTIME(),UserLogin,'wsp_GetUserAccess','SP Called','1');
 
   COMMIT;
 
 OPEN c1 using DCode, UserLogin;
 
--insert into testing2 values (SQLStmtSelect);
 
 
 
 
 
END

Select allOpen in new window