Solved

Temp Tables called in Sybase by other Procedures

Posted on 2003-11-14
6
3,361 Views
Last Modified: 2008-06-03
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
Comment
Question by:valiant_at_mci
  • 3
6 Comments
 
LVL 4

Accepted Solution

by:
sybasetogo earned 250 total points
ID: 9751586
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9758259
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9818424
got any feedback ? how did you get on ?
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 10042489
please return to this question
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase ASE BCP In With Table with No Identity Column 1 1,190
Generating a Format File in Sybase ASE BCP 1 1,167
Clean up a mailbox 5 156
Cluster Resource error 3 61
When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
Read about the ways of improving workplace communication.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question