• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3629
  • Last Modified:

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.
0
valiant_at_mci
Asked:
valiant_at_mci
  • 3
1 Solution
 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now