In SQL Server I can fill a temporary table with the data return from a stored procedure.
If I have the stored procedure sp_x like that

create procedure sp_x
as
select a, b from c

Then I create a temporary table temp with two columns similar to those returned by sp_x.

Then I use or
insert into temp
exec sp_x

And the temporary table will receive the data that the sp_x return.
Can you tell me if it is possible to do that with DB2?
Thanks.

I don't think so

you will have to specify the insert as
part of the stored procedure

a: begin

yoursqlinsertstatement

end a

Please chech the PL reference

HTH

if i did not misunderstand, it is possible.first piece of code below -EXAMPLECALLER- is a stored procedure that call the second stored procedure -EXAMPLE2, takes the result set of EXAMPLE2, creates a temp table, and fills the temporary table with the results, then returns the results to the screen as it selects and creates a result set from the temp table(containig result set of EXAMPLE2)


*******SQL stored procedure (db2)***********
CREATE PROCEDURE DB2ADMIN.EXAMPLECALLER (out var2 varchar(15),
out var3 varchar(15),out var4 int)
SPECIFIC DB2ADMIN.EXAMPLECALLER
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

--It works
-- it calls a stored proc in the same database and return the result set
------------------------------------------------------------------------
P1: BEGIN

DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPSALES
(COLUMN2 VARCHAR(15),COLUMN3 VARCHAR(15),COLUMN4 int)
WITH REPLACE
ON COMMIT
PRESERVE ROWS
NOT LOGGED
in BIG_TABLESPACE ;

BEGIN
DECLARE result1 RESULT_SET_LOCATOR VARYING;
DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM SESSION.TEMPSALES;

CALL EXAMPLE2();
ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE2;
ALLOCATE rsCur CURSOR FOR RESULT SET result1;

WHILE (at_end = 0) DO
FETCH FROM rsCur INTO var2,var3,var4;
INSERT into SESSION.TEMPSALES values(var2,var3,var4) ;
END WHILE;

OPEN cursor2;

END;



END P1

***************END OF Stored procedure********************


************EXAMPLE2 stored procedure***************
CREATE PROCEDURE DB2ADMIN.EXAMPLE6 ( )
SPECIFIC DB2ADMIN.EXAMPLE6
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure that is called
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor

DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
SELECT
DB2ADMIN.SALES.SALES_PERSON AS SALES_PERSON,
DB2ADMIN.SALES.REGION AS REGION,
DB2ADMIN.SALES.SALES AS SALES
FROM
DB2ADMIN.SALES;

-- Cursor left open for client application
OPEN cursor2;


END P1

***********end of EXAMPLE2******************