I have found that this works for me..
SET PATH *LIBL ;
CREATE PROCEDURE MYLIB.SP_GETSVCC (
IN I_SITEID DECIMAL(3, 0) ,
IN I_ACCT DECIMAL(9, 0) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC COHPGMS.SP_GETSVCC
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX
BEGIN DECLARE C1 CURSOR FOR SELECT I0NUO9 , I0CEK6 , I0NUPA FROM CBI0REL1 WHERE I0NROV = I_SITEID AND I0CNBR = I_ACCT ; OPEN C1 ; END ;
COMMENT ON SPECIFIC PROCEDURE COHPGMS.SP_GETSVCC
IS 'Gets the service categories for an account' ;
COMMENT ON PARAMETER SPECIFIC PROCEDURE COHPGMS.SP_GETSVCC
( I_SITEID IS 'Site ID' ,
I_ACCT IS 'Account Number' ) ;
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
P Go Green! Print this email only when necessary. Thank you for helping Time Warner Cable be environmentally responsible.
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Wednesday, June 10, 2009 9:38 AM
To: .net use with the System i
Subject: [SystemiDotNet] Stored Procedures
Hello.
I'm trying to create a stored procedure in Navigator that will return a
result set. How do I define and return the cursor? Also, should I be
closing the cursor?
I have one IN param = CustomerNumber
This does not work for me...
DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
select * from amflib.mthacte where cusno = CustomerNumber;
OPEN CUR1;
SET RESULT SETS CURSOR CUR1;
Thanks for any insight.
midrange.com
