RE: SQL Stored Procedure

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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