Hi,
I have written one stored procedure.
What I am trying to do is:
1. get the data from base table.
2. modify the data of one column(FOLDERNAME)
Well here, value of foldername looks like
"c:\folder\Myfolder". But I want only Myfolder from this data. So we
have written another stored procedure which extract the data and
store it in output parameter, because there is no inbuilt support
from sapdb for doing all this.
3. Copy the data got from base table + modified foldername in to temp
table. This temp table is created at runtime only in the stored procedure itself.
4. Sort the temp table on foldername.
5. Drop the temp table.
6. Return it as a cursor.
It is must that I dont touch the original data, but also data must be
sorted on the modified foldername and returned it as cursor.
All the above I discussed is working fine.
Now problem is that temp table creation and copying data to temp table is an expensive
operation. So if there are 10,000 records to be copied to the temp table at runtime,
it will affect the performance of the system.
Is there any other way of doing all this?????
Here is the code for stored procedure:
call UPDATERESULTSET_CURSOR3
I guess something is wrong with temp table, but dont know what??
Here is the stored procedure
CREATE DBPROC UPDATERESULTSET_CURSOR3 returns CURSOR AS
VAR
JINDEX INT;
JID VARCHAR(64);
CUSERID INT;
AUSERID INT;
FLEINDEX INT;
FLDRNAME VARCHAR(255);
FLENAME VARCHAR(1024);
JNAME VARCHAR(255);
TMPFLDRNAME VARCHAR(1024);
WRKFLOWFILENAME VARCHAR(1024);
ARCHIEVEFLDRNAME VARCHAR(1024);
TRGTNAME VARCHAR(255);
USRNAME VARCHAR(255);
JNO INT;
CHANGEDFLDRNAME VARCHAR(255);
BEGIN
CREATE TABLE TEMP.TEMPJOBS(TMPJOBINDEX INT,
TMPJOBID VARCHAR(64),
TMPCREATEDUSERID INT,
TMPASSIGNEDUSERID INT,
TMPFILEINDEX INT,
TMPFLDRNAME VARCHAR(255),
TMPFILENAME VARCHAR(1024),
TMPJOBNAME VARCHAR(255),
TMPTEMPFLDRNAME VARCHAR(1024),
TMPWRKFLOWFILENAME VARCHAR(1024),
TMPARCHIEVEFLDRNAME VARCHAR(1024),
TMPTARGETNAME VARCHAR(255),
TMPUSERNAME VARCHAR(255),
TMPJOBNO INT);
DECLARE MYCURSOR CURSOR FOR
SELECT * FROM PMP_OWNER.JOBS;
WHILE $RC = 0 DO BEGIN
FETCH NEXT MYCURSOR
INTO :jIndex, :jID, :cUserID, :aUserID, :fleIndex, :fldrName,
:fleName, :jName, :tmpFldrName, :WrkFlowFileName, :ArchieveFld
rName,
:TrgtName, :UsrName, :jNo;
IF($rc = 100) THEN break;
CALL GETFOLDERNAME2(:fldrName, :changedFldrName);
INSERT INTO TEMP.TEMPJOBS(TMPJOBINDEX ,
TMPJOBID,
TMPCREATEDUSERID,
TMPASSIGNEDUSERID,
TMPFILEINDEX,
TMPFLDRNAME,
TMPFILENAME,
TMPJOBNAME,
TMPTEMPFLDRNAME,
TMPWRKFLOWFILENAME,
TMPARCHIEVEFLDRNAME,
TMPTARGETNAME,
TMPUSERNAME,
TMPJOBNO)
VALUES(:jIndex,
:jID,
:cUserID,
:aUserID,
:fleIndex,
:changedFldrName,
:fleName,
:jName,
:changedFldrName,
:WrkFlowFileName,
:ArchieveFldrName,
:TrgtName,
:UsrName,
:jNo);
END;
DECLARE ReturnCursor CURSOR FOR
Select * from TEMP.TEMPJOBS order by TMPFLDRNAME;
/*Drop Table, as we dont need it*/
DROP Table TEMP.TempJOBS;
$CURSOR = 'ReturnCursor2';
DECLARE $CURSOR CURSOR FOR
Select * from PMP_OWNER.ReturnCursor;
END;
---------------------------------
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
