RE: Stored Procedures

Darrell,

Below are SQL statements that create a stored procedure I use to call an
RPGLE and returns 4 parameters to an ASP.NET application.

CREATE PROCEDURE ODBCLIB.SP_ATTSTAT (
IN DISTRICT CHAR(3) ,
IN SCHOOL CHAR(3) ,
IN STUDENTID CHAR(9) ,
IN COURSE CHAR(6) ,
IN SECTION CHAR(3) ,
IN PERIOD CHAR(2) ,
IN TERM CHAR(2) ,
INOUT DREASON CHAR(2) ,
INOUT ATYPE CHAR(1) ,
INOUT PREASON CHAR(2) ,
INOUT STATUS CHAR(2) )
LANGUAGE RPGLE
SPECIFIC ODBCLIB.SP_ATTSTAT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'ODBCLIB/ATTSTATUS'
PARAMETER STYLE GENERAL ;

COMMENT ON SPECIFIC PROCEDURE ODBCLIB.SP_ATTSTAT
IS 'Retrieve Online Student Attendance Status' ;

Here is another that returns a dataset to a Gridview in ASP.NET

SET PATH "QSYS","QSYS2";

CREATE PROCEDURE ODBCLIB.SP_TSCDTA (
IN STUDENT CHAR(9) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC ODBCLIB.SP_TSCDTA
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,
COMMIT = *NONE,
DECRESULT = (31, 31, 00),
DFTRDBCOL = *NONE,
DYNDFTCOL = *NO,
DYNUSRPRF = *USER,
SRTSEQ = *HEX
BEGIN

DECLARE VIEWCNT CURSOR FOR

SELECT
CONFNAME, CONLNAME, RTPEDESC, CONCALLSEQ,
REPLACE(REPLACE(CONRESTYPE,'0','N'),'1','Y') AS
CONRESTYPE
FROM SBCUSFIL.PSTU705
JOIN SBCUSFIL.PSTU709 ON CONRELTYPE = RTPEID
WHERE RTPEACTIVE = 'Y' AND CONSTUID = DEC(STUDENT,9,0)
ORDER BY CONCALLSEQ, CONRESTYPE DESC;

OPEN VIEWCNT;
SET RESULT SETS CURSOR VIEWCNT;

END;

COMMENT ON SPECIFIC PROCEDURE ODBCLIB.SP_TSCDTA
IS 'Trade school request web page contact data';

Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak

Notice: This message or any accompanying documents may contain
confidential or privileged information of Hanover County Public Schools.
If you are not the intended recipient, disclosure, copying or
distribution is strictly prohibited by state and federal law. If you
received this message in error, please notify the sender as soon as
possible.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of DLee@xxxxxxxx
Sent: Wednesday, March 18, 2009 10:22 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Stored Procedures

Thanks to all who responded, that cleared the issue up for me,

Any suggestions where I can get some examples of stored procedures,
using
sql?

Darrell Lee
Information Technology
Extension 17127