Re: SQR and Stored Procedure

[Ryszard Jedrychowski <Ryszard_Jedrychowski@CARDLINK.CO.NZ>]

I do not know how it will work in SQR but if I want to use temporary table
in two stored procedures then the hash table must be created before
compiling the second stored procedure e.g:

create table #temp1(
a int not null,
b char(10) null,
c datetime null,
etc..)

create procedure stored_proc2
as
begin
select a,b,c, etc.
into #temp2
from #temp1
where bla,bla,bla
end


create procedure stored_proc1
as
begin
select a,b,c, etc.
into #temp1
from table1
where bla,bla,bla
...
exec stored_proc2
...
end

Ryszard Jedrychowski





jpbowie@HOTMAIL.COM on 14/09/99 03:07:29 AM

Please respond to SQR-USERS@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Ryszard Jedrychowski/Cardlink/NZ)
Subject:  SQR and Stored Procedure




Does anyone have experience with creating a temporary table in a stored
procedure and referencing it from within SQR?

My Problem:

I have a stored procedure that performs several table updates and generates
2 temporary tables.  One table is for data that cannot be updated or added
because it contains errors, and the second is a table for new information
that has been added/updated to the permanent tables.  I need to reference
these temporary tables in an sqr program to generate two reports (obviously
an error report and a report containing data that has changed).  I have
created an SQR program that uses the EXECUTE statement with a -C option to
specify the connection.  I then use two BEGIN-SELECT statements with a -C
option to select the information from my temporary tables.  When I execute
the code, I get an error stating that the temporary tables do not exist.  I
have searched through the archives for this mailing list and have tried the
solutions suggested there, none seem to work.  Any suggestions?

My Environment:

AIX v4
SQR v4.0.1.1
SYBASE 11.5.1

Thanks,
James Bowie

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com