Solved

Temp Tables called in Sybase by other Procedures

Posted on 2003-11-14
6
3,322 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A safe way to clean winsxs folder from your windows server 2008 R2 editions
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now