Solved

Temp Tables called in Sybase by other Procedures

Posted on 2003-11-14
6
3,400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
Read our guide on how to survive being on-call.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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