Stored Procedures

[Guest.Visitor]

Arun, I don't know this area that well, but what if you tried making it a scrollable cursor? C/EXEC SQL DECLARE RCURSOR scroll CURSOR FOR Also, I notice that you are doing a Select *. If you don't need all of the fields, and it is a large row size, I would definitely recommend explicitly selecting the needed fields. Bill > The Problem is that in the database of it has 10,000 records it sends all those to the client server side which takes time in processing. Istead I want to send a few record intially and when the user presses a page down then the next few record I need to send. This should work exactly same like the subfile. This will make the application faster. I neeed some help to go ahead with this part. > > SQL RPG program which is called from the Stored Procedure. > ------------------------------------------------------------ > FRsnmstl0 IF E K DISK > * Reason master file St. Louis. > * > D TEST UDS 10 > D TEST1 1 5 > D TEST2 6 10 > * > * Declare cursor. > > C/EXEC SQL DECLARE RCURSOR CURSOR FOR > C+ SELECT * FROM RSNMSTL0 > C/END-EXEC > > * Open the cursor. > > C/EXEC SQL OPEN RCURSOR > C/END-EXEC > > * Keep the cursor opened and return it as record set. > > C/EXEC SQL SET RESULT SETS CURSOR RCURSOR > C/END-EXEC > * > C IF SQLSTT = '02000' > C RETURN > C ELSE > C EVAL TEST2 = 'BBBBB' > C RETURN > C ENDIF > ------------------------------------------------------------------------- -- > > Used the following statement to create the stored procedure. > Specify that the return value will be record set while creating the > stored procedure. > > CREATE PROCEDURE BWIR/RESON > RESULT SETS 1 > NOT DETERMINISTIC READS SQL DATA EXTERNAL NAME BWIR/REASON > PARAMETER STYLE GENERAL > > Thanks in advance, > > Arun.K.Gopinath

[Guest.Visitor]

Thanks Bill, I will try using scroll cursor. The source code I posted was a sample one and I am going to select based on come condition only. But in that case too in some selection criteria there is more than 10,000 records. So one more help I want is to build the select conditions based on varying where condition. In this case the user can run a query on the VB application which will inturn call my stored procedure with different selection criteria. May be they key in all the criteria such as item number, description, unit of meassure etc ..... or in other case they may give one or more of these selection criteria. In this circumstance how do I build the SQL statement. Thanks in Advance Arun.

[Guest.Visitor]

Arun, Here's how I'd do it in VB:

 cWhereStmt = " " If cItemNumber <> " " then cWhereStmt = cWhereStmt & " ItemNumber = " & cItemNumber & " and " End If If cDescription <> " " then cWhereStmt = cWhereStmt & " Item Description Like " & cItemDescription & " and " End If If cUM <> " " then cWhereStmt = cWhereStmt & " UnitMeas = " & cUM & " and " End If << insert code to remove the last -and- >> If cWhereStmt <> " " then cWhereStmt = "Where " & cWhereStmt End If cSelect Stmt = "Select fielda, fieldb From file1 " & cWhereStmt & " Order by 1,2 " 

Bill > In this case the user can run a query on the VB application which will inturn call my stored procedure with different selection criteria. May be they key in all the criteria such as item number, description, unit of meassure etc ..... or in other case they may give one or more of these selection criteria. In this circumstance how do I build the SQL statement.

[Guest.Visitor]

All, I have a typival problem. I have a stored procedure which returns a result set to the calling Visual Basic program. It works fine. I have given the source code for the Stored procedure and the SQLRPG program it calls. The Problem is that in the database of it has 10,000 records it sends all those to the client server side which takes time in processing. Istead I want to send a few record intially and when the user presses a page down then the next few record I need to send. This should work exactly same like the subfile. This will make the application faster. I neeed some help to go ahead with this part. SQL RPG program which is called from the Stored Procedure. ------------------------------------------------------------ FRsnmstl0 IF E K DISK * Reason master file St. Louis. * D TEST UDS 10 D TEST1 1 5 D TEST2 6 10 * * Declare cursor. C/EXEC SQL DECLARE RCURSOR CURSOR FOR C+ SELECT * FROM RSNMSTL0 C/END-EXEC * Open the cursor. C/EXEC SQL OPEN RCURSOR C/END-EXEC * Keep the cursor opened and return it as record set. C/EXEC SQL SET RESULT SETS CURSOR RCURSOR C/END-EXEC * C IF SQLSTT = '02000' C RETURN C ELSE C EVAL TEST2 = 'BBBBB' C RETURN C ENDIF --------------------------------------------------------------------------- Used the following statement to create the stored procedure. Specify that the return value will be record set while creating the stored procedure. CREATE PROCEDURE BWIR/RESON RESULT SETS 1 NOT DETERMINISTIC READS SQL DATA EXTERNAL NAME BWIR/REASON PARAMETER STYLE GENERAL Thanks in advance, Arun.K.Gopinath