RE: SQL Stored Procedure

Here is a little dynamic SQL example to get you going:

CREATE PROCEDURE PROCNAME
(IN SQL_STATEMENT CHAR (2000),
IN NFROM INT,
IN NTO INT)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE MyCursor CURSOR FOR MyPreparedStmt;
PREPARE MyPreparedStmt FROM SQL_STATEMENT;
OPEN MyCursor;
END;

/* to test it, invoke it with an sql statement like this */
CALL PROCNAME('SELECT * FROM SYSIBM/SYSDUMMY1',0,0);

You'll notice that I ignored the NFROM and NTO input arguments. That's
because I'm confused with what you mean by row #. Do you mean relative
record number (RRN)?
Your reference to DYNAMIC SCROLL CURSOR implies you'd like to fetch a
relative record number from a scrolling cursor, but that doesn't make sense
in the context of a stored procedure that returns a result set. I mean,
either you fetch to a host variable or return a result set. Which is it?
Or do you have a primary key column in the table and want to use the NFROM
and NTO arguments in a BETWEEN clause?

FYI, there are some nice examples in the IBM redbook "Stored Procedures,
Triggers, and User-Defined Functions on DB2 Universal Database for iSeries"
which you can download at this link:

http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Hth, Elvis


Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: SQL Stored Procedure

I am trying to create a stored procedure that accepts an SQL statement,
a FROM row #, & a TO row number.

If I could ask your help with the syntax on where to put the row logic
(or just the syntax in general) I would appreciate it.

CREATE PROCEDURE LIBRARY/PROCNAME
(IN SQL_STATEMENT CHAR (2000),
IN NFROM INT,
IN NTO INT)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE C1 DYNAMIC SCROLL CURSOR
WITH RETURN TO CALLER
FOR SQL_STATEMENT;
OPEN C1;
FETCH FROM C1;
CLOSE C1;
END

Thanks.
James Salter