Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SPs & temp tables

3 posts in Product Futures Discussion Last posting was on 2003-03-23 16:03:17.0Z
Carl Kayser Posted on 2003-03-17 18:29:25.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Subject: SPs & temp tables
Date: Mon, 17 Mar 2003 13:29:25 -0500
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <uBk3xNL7CHA.52@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1021
Article PK: 95261

This is not the kind of request that I like to make since it only dsecribes
the problem but doesn't describe a solution.

What I would like is (somehow) for one to reverse engineer stored procs that
communicate via temporary tables. One has to define the procs and
create/drop the temp tables in correct order. Note also that if one does
not have the two create table statements exactly identical then you may get
execution time errors. Or not. (Obvious example is when a column differs
in nullability in the two statements. It may be a while before one tries to
insert a NULL value into the column.) Apparently, this situation is
"impossible" for SQL-BackTrack to solve when doing logical database loads.
This is my main motivation for wanting this kind of request.

I'm a firm believer in maintaining source code. But sometimes one wants to
reload from scratch. And I don't like the fact that one can mismatch the
two create table statements and not receive a compilation error.

Do Oracle, DB2, or SQL Server provide this functionality? If so, do they
provide it more cleanly?


putnamr Posted on 2003-03-22 17:20:10.0Z
From: putnamr@river.it.gvsu.edu
Date: Sat, 22 Mar 2003 12:20:10 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: SPs & temp tables
Message-ID: <F5F6008B1540342E005F3AAE85256CF1.006C921F85256CEC@webforums>
References: <uBk3xNL7CHA.52@forums-1-dub>
Lines: 25
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1037
Article PK: 95276

Hello,

All stored procedures that we have that use temp tables, whether they span
multiple sps or not, have to have a defined with header that describes the
temp table:

/* BEGIN TEMP TABLE DECLARATION AREA
TEMP TABLE
TEMP TABLE
TEMP TABLE
*/ END TEMP TABLE DECLARATION AREA

When we extract the procs later we parse the text and create the temp
tables and then drop them.

Took time to perfect this though.

Maybe Sybase should create a new table to store the create statements for
temp tables by associated object so that one may easily get at the temp
table create statement.

Sound Reasonable?

Thanks,
Ryan Putnam


Carl Kayser Posted on 2003-03-23 16:03:17.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <uBk3xNL7CHA.52@forums-1-dub> <F5F6008B1540342E005F3AAE85256CF1.006C921F85256CEC@webforums>
Subject: Re: SPs & temp tables
Date: Sun, 23 Mar 2003 11:03:17 -0500
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <OZEfFYV8CHA.52@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1049
Article PK: 95288


<putnamr@river.it.gvsu.edu> wrote in message
news:F5F6008B1540342E005F3AAE85256CF1.006C921F85256CEC@webforums...
> Hello,
>
> All stored procedures that we have that use temp tables, whether they span
> multiple sps or not, have to have a defined with header that describes the
> temp table:
>
> /* BEGIN TEMP TABLE DECLARATION AREA
> TEMP TABLE
> TEMP TABLE
> TEMP TABLE
> */ END TEMP TABLE DECLARATION AREA
>
> When we extract the procs later we parse the text and create the temp
> tables and then drop them.
>
> Took time to perfect this though.
>

Basically this is a configuration management factor. There could be one
proc per file, many procs per file, or one "everything" file. (I certainly
wouldn't care to have either of the two extremes.) What exacerbates the
issue is that two different procedure usages could use different temporary
tables that have the same name (although I believe that you address this
issue with your technique). And only the first ten characters are
significant.

> Maybe Sybase should create a new table to store the create statements for
> temp tables by associated object so that one may easily get at the temp
> table create statement.
>

Offhand, it certainly sounds interesting. It might even eliminate the need
for the table creation outside of the driver proc generation! (Now one
would reverse the order of proc definitions within a file and call by name
instead of id at execution time of the driver. And if a driver were
recompiled the subs would also have to be recompiled to resolve the temp
table definition id.) This would also facilitate third party tools such as
SQL-BackTrack. It might make difficulties for maintaining sysdepends with
respect to subroutines.

> Sound Reasonable?
>
> Thanks,
> Ryan Putnam