Temp Tables called in Sybase by other Procedures

I am running Sybase 12.5

I essentially need the set up for how to call a nexted procedure (calling one procedure from inside another), and being able to reference the temp table that is generated in the parent.

Essentially:

Procedure #1 does calculations and stored data to 3 temp tables.
Procedure #1a - References a table generated in Procedure #1
Procedure #1b - References a table generated in Procedure #1

I don't have to return any values back from the sub procedures.

I'm looking for the syntax for calling a nested procedure in 12.5 and how to address the table that was generated in the parent procedure.

I have tried setting up the table as ##<global table>, but when I try to compile procedure 1a, it can not find the object and won't compile the procedure.

Thanks much.
valiant_at_mciAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sybasetogoCommented:
In single ISQL session:

create table #tmp (a int)
GO
create procedure n2 as
begin
update #tmp set a = 2
end
GO
create procedure n3 as
begin
update #tmp set a =3
end
GO
create procedre n1 as
BEGIN
create table #tmp1 (a int)
insert into #tmp1 values (1)
exec n2
exec n3
END
GO
The trick is to create all required temporary tables at session level and complile all nested stored procedures IN SAME SESSION. That will allow sybase to resolve external temp table name during compile time. At execution time top level stored proc will create required temp tables.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisKingCommented:
sybasetogo is basically right (bar the type in "create procedure n1" and the mix of #tmp and #tmp1)

If using a single session, I believe you will want to drop the temp table again before the create procedure n1

all nested procedures do not have to be run in the same session. Each child/nested procedure that uses a temp table created in a parent procedure needs the temp table to be created before the procedure is created, the same applies to if you need to execute the procedure directly.

you could run this in in 3 distinct sessions
------------------- SESSION 1 -------------------
create table #tmp (a int)
GO
create procedure n2
as
    update #tmp set a = 2
GO
drop table #tmp
GO
------------------- SESSION 2 -------------------
create table #tmp (a int)
GO
create procedure n3
as
    update #tmp set a =3
GO
drop table #tmp
GO
------------------- SESSION 3 -------------------
create procedre n1
as
    create table #tmp (a int)
    insert into #tmp values (1)
    exec n2
    exec n3
    drop table #tmp
GO
0
ChrisKingCommented:
got any feedback ? how did you get on ?
0
ChrisKingCommented:
please return to this question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.