drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
 Posted: Wed May 06, 2015 5:53 pm Post subject: Cursor with return for |
 |
|
We have a stored procedure that returns result set to a web service. In addition to the columns selected from tables, I need to also return some computed values that involves complex business logic. I can load the computed host variable (HV1, HV2, HV3) in a new table and join it in the cursor statement, but having a table to hold short lived data is not convincing to me. It also requires table clean up process and DBA overhead. Is there a way to return the host variables in the SQL?
| Code: |
DECLARE CUSTOMER_CURSOR WITH RETURN FOR
SELECT NAME, ADDR, PHONE,
CASE WHEN TYPE = 'A' THEN HV1
WHEN TYPE = 'B' THEN HV2
ELSE HV3
END COMPLEX_COMPUTED_VAL
FROM CUST_TABLE
WHERE ...
|
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed May 06, 2015 6:43 pm Post subject: |
|
|
When you create the Procedure you can set it up for INOUT.
Sample COBOL stored procedure.
| Code: |
CREATE PROCEDURE XXXXX.GETXXXXX
(INOUT SSN INTEGER,
INOUT ANSWER CHAR(01))
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME XXXXXXXXXXXX
COLLID XXXXXXXX
SECURITY DB2
WLM ENVIRONMENT DSNWLM_GENERAL; |
_________________
Thanks,
NASCAR9 |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
