Re: New to DB2/400 Procedures

Steve, you are right.  The procedure is working now.  Thanks much. RH
"Steve Richter" <stephenrichter@xxxxxxxxx>
wrote in message
news:3371cf510605031707n41cd5a23qecf583adebdec49f@xxxxxxxxxxxxxxxxx
> 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
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>