× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.