var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Returning Records from Stored Procedures
I have to write some complex logic in stored procedures and I see that the 'common table' syntax in db2 sql would be helpful.
But I don't get how the DECLARE CURSOR RETURN FOR CLIENT and
OPEN CURSOR syntax requirements are going to work out for this.
How do I open temporary/common tables in a stored procedure and then get them into a cursor and return the records?
For me the goal would be to flexibly create a recordset/table on the fly from a range of sources and techniques and then return the version of it that I want to define as the 'final result'.
So again, how does the OPEN CURSOR syntax work?
If I just start by trying to create a table like:
WITH TEMP1 AS
(SELECT SOME STUFF);
I immediately wonder what I'm doing.
In order to create, fill, the select and return data from a temporary table, you need to perform a little trick to get around the syntax.
Create procedure myproc(...)
DECLARE GLOBAL TEMPORARY TABLE fred ...
INSERT INTO SESSION.FRED ...
DECLARE CURSOR1 CURSOR WITH RETURN TO CLIENT FOR SELECT * from SESSION.FRED;
So I can see how that would work, and I recognize that I can look up a lot of the details in the docs.
But I read somewhere you can create joins and such with the global tables.
So how about this?
DECLARE GLOBAL TABLE FRED;
INSERT INTO SESSION.FRED SELECT * FROM tblFriends;
DECLARE GLOBAL TABLE FRED2 ;
INSERT INTO SESSION.FRED2 SELECT * FROM tblFoes;
DECLARE GLOBAL TABLE WARFRED;
INSERT INTO SESSION.WARFRED SELECT FRIENDS.*, FOES.* FROM SESSION.FRED FRIENDS INNER JOIN SESSION.FRED2 FOES;
DECLARE CURSOR WARANDPEACE FOR RETURN TO CLIENT
OPEN CUSOR WARANDPEACE