RE: New to DB2/400 Procedures

["Richter,Steve" <Steve.Richter@xxxxxxxxxxxxxxx>]

try something like this Ryan:

 Create procedure testproc1(
 in @DOCOID int,
  in @ConID smallint )              
 Language        SQL                        
 modifies        SQL DATA                   
 result sets     1                          
                                            
 SET OPTION  COMMIT = *NONE                 
 BEGIN                                      
                                            
 declare         c1 cursor with return for  

 SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
 INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
 INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
t3.nhiwcontacttypeid
 WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = 7

 for read only ;                            
                                            
 open            c1 ;                       
 end                                        

-----Original Message-----
From: Ryan Hunt [mailto:ryan.hunt@xxxxxxxxxxxxx]
Sent: Wednesday, May 03, 2006 11:45 AM
To: midrange-l@xxxxxxxxxxxx
Subject: New to DB2/400 Procedures


OK, I have lots of reading to do...I know that.  I'm completely unfamilar
with the way DB2 result sets and flow-control language are used in SP's (as
I understand it, DB2 requires the use of cursors - probably based on the
ANSI standard).

Can someone help me get jump started here...  Below is a Proc using MS SQL's
T-SQL (which is what I'm used to.)  Can someone do a quick translation?


CREATE PROCEDURE spGetContact (@DOCOID int, @ConID smallint)
AS

DECLARE @NDOCOID Integer
DECLARE @CONTACTID SmallInt

SET @NDOCOID=@DOCOID
SET @ContactID=@ConID

IF EXISTS(
SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE WHERE NDOCO=@NDOCOID AND
NHIWCONTACTTYPEID=@CONTACTID
)
BEGIN

 SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
 INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
 INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
t3.nhiwcontacttypeid
 WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = @CONTACTID
END

ELSE
BEGIN
 SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1
 INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid
 INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
t3.nhiwcontacttypeid
 WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = 7

END

GO