Because iam sick and tired of this next error: "%1 returned a result
set with a
different schema than expected" , I thought, why not use temporary
tables??

I'm selecting all data from a stored procedure with like 30 columns
from joined tables in it in a second stored procedure and created
extra columns with calculated values and the 30 columns from the first
stored proc in it. This second stored proc also has a subquery in it
and iam using parameters to fetch the data out of the first stored
proc.

I need the stored procedure for a .net application to create csv
files.

My question is how to use/create a temporary table in the first stored
procedure and fetch the data from this table in the second stored
proc. with parameters?

This is my start

ALTER PROCEDURE "DBA"."storedproc1"(
in iTest char(1)

BEGIN
insert into #temp
select column1,,,,,column30 from testA
end



ALTER PROCEDURE "DBA"."storedproc2"(
in iTest char(1)

BEGIN
????
select column1,,,,,column30 , columnextra as extra from storedproc1
(iTest)
?????
end

I think I solved the problem my self

ALTER PROCEDURE "DBA"."storedproc1"(
in iTest char(1)


BEGIN
insert into #temp
select column1,,,,,column30 from testA

select * from #temp <--

end


ALTER PROCEDURE "DBA"."storedproc2"(
in iTest char(1)


BEGIN

select column1,,,,,column30 , columnextra as extra from storedproc1
(iTest)

end

Janis,

even if you found a solution, you might want to take a look at the
CREATE LOCAL TEMPORARY TABLE ...
statement.

In SP1 you could fill the table and in SP2 select from it.

Reimer

janis wrote:

I'm not exactly certain how the use of temporary tables
(alone) could help out much here.

But given that your desired end result is a comma delimited
formatted file have you considered the use of the string( )
function to build the lines for you? You may just find you
can select of all the desired columns and values as a long
varchar that way and be very abitrary about which.

For example

select
string(
ID,',',Surname,',',GivenName,',',Street,',',City,' ,',State,',',Country,',',PostalCode,',',Phone,',', CompanyName
)
from Customers where . . .

will bring back a single varchar column with the values seperated
by commas. Adding single quotes is also possible if you want
with constructs like

select string( '''', col1 , ''',''', var1, ''',''',....,'''' )
from ....

and if you want control over the delimiter style you
can use variables (up to 3) for much of this. Say,
like this example:
begin
declare del_strt varchar(20);
declare del_mdl varchar(20);
declare del_end varchar(20);
set del_strt = '''''';
set del_mdl = ''''',''''';
set del_end = '''''';
select string( del_strt, col1 , del_mdl, var1, del_mdl,....,
del_end ) from ... ;
end;


Just a thought. HIH.



"janis" <janimie (AT) gmail (DOT) com> wrote