Re: New to DB2/400 Procedures

On 5/3/06, Ryan Hunt <ryan.hunt@xxxxxxxxxxxxx> wrote:

OK, your example worked...but my slight variation does not:

Create procedure PRODDTA.spGetContactTest2(in @DOCOID int,  in @ConID
smallint )
 Language        SQL
 modifies        SQL DATA
 result sets     1

 SET OPTION  COMMIT = *NONE

 BEGIN
IF EXISTS(
SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE WHERE NDOCO=@DOCOID AND
NHIWCONTACTTYPEID=@CONID
)
THEN
 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 RODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid =
t3.nhiwcontacttypeid
WHERE NDOCO = @DOCOID AND t1.NHIWCONTACTTYPEID = @CONID
 for read only ;

 open c1 ;

all the declares in the procedure, whether variables or cursors, have
to be specified before any procedure code.   You might be able to
declare two or more cursors, then open only one depending on your
conditional code.

-Steve