|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
Hi,
Can i open a cursor in a calling stored procedure which is declared in a called stored procedure for a sql query. Basically i am calling a already existing stored procdure from a newly created one problem, the problem i am facing is that, i am not able to retreive the result set data by this methiod which is possible if i call the existing stored proc directly. All these stored procedures are written in SQL.
Thanks in advance for any tips |
|
| Back to top |
|
 |
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi
The requirement in too much confusing, can you just elaborate the requirement,
What lead to such system design ? |
|
| Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Hi rakesh,
Are you trying to get the results of a cursor which you have opened in the called program from the calling program?? |
|
| Back to top |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
to be more eloborate.. i have this stored procedure SP1 which earlier was displaying the results set when called directly from the client(DB2 connect) i use.. now i have a requirement to call this SP1 from another stored procedure SP2 and still display the same results of SP1.
Is this possible
by just calling SP1 from SP2 .SP1 has already the cursors declared and open when called. or is there any other way through this ?? |
|
| Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Hi Rakesh,
Yes..You can call the Stored Procedure from another stored Procedure.
The step used to call is as below in the SP2:
CALL SYSPROC.SP1 (:INPUT1
,:INPUT2
,:OUTPUT1)
All the input and output parameters have to be listed out.
The output parameter results will be obtained directly from SP1 in SP2 through OUTPUT1 parameter.
If you are trying to get the Resultset of the cursor , then you will have to use ASSOCIATE cursors, ALLOCATE them and handle them using +466 SQLCODE. |
|
| Back to top |
|
|
aryanpa1
New User
Joined: 26 May 2007
Posts: 45
Location: Chennai
|
|
|
|
Hi Rakesh ,
This is possible to do. How are you handling the resultset coming from SP1 in SP2 and wat are the probelms you are facing. |
|
| Back to top |
|
|
aryanpa1
New User
Joined: 26 May 2007
Posts: 45
Location: Chennai
|
|
|
|
Hi Rakesh ,
Try this Code
| Code: |
01 WS-LOC USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
:
:
CALL SYSPROC.SP1 (:INPUT1
,:INPUT2
,:OUTPUT1)
EVALUATE SQLCODE
WHEN 0
WHEN 466
EXEC SQL
ASSOCIATE LOCATORS (:WS-LOC)
WITH PROCEDURE SP1
END-EXEC
EXEC SQL
ALLOCATE SP1_CSR CURSOR
FOR RESULT SET :WS-LOC
END-EXEC
EXEC SQL
FETCH SP1_CSR
INTO :HOST-VAR1,
:HOST-VAR2
END-EXEC.
:
:
: |
|
|
| Back to top |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
thanks aryan and Pavan but what am looking for is stored procedures written in SQL language not COBOL..
currently what i have done is ..i have got the SQL statement formed in the called stored procedure, passed it back to the calling stored procedure via an output parameter and then back in SP1 the cursors are deployed to display the result set |
|
| Back to top |
|
|
|
|
