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.

temp tables in recursive sprocs

5 posts in General Discussion Last posting was on 2009-05-21 13:46:53.0Z
Gareth Davies Posted on 2009-05-18 14:50:38.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: temp tables in recursive sprocs
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a116512$1@forums-3-dub.sybase.com>
X-Original-Trace: 18 May 2009 06:39:30 -0700, vip152.sybase.com
Lines: 32
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 18 May 2009 07:50:33 -0700, forums-3-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 May 2009 07:50:38 -0700
X-Trace: forums-1-dub 1242658238 10.22.108.75 (18 May 2009 07:50:38 -0700)
X-Original-Trace: 18 May 2009 07:50:38 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27720
Article PK: 77007

Ran into an annoying problem and am hoping some of you may have some advice.

We have a recursive legacy sproc (for tree traversal) that stores data
in a temp table created by various sprocs that call it.

The usual method to create the sproc (creating the table so it's in
scope and deleting it after again) is not suitable because we reverse
out a file to build all sprocs from a standard DB and of course this
topping and tailing code isn't in syscomments, so the build file won't run.

I thought I'd come up with a clever solution to this by putting this
never-to-execute code in the recursive sproc:

if 1=2
begin
create #temp (blah)
end


It certainly shut the compiler up at run time, but in fact, the temp
table of the parent sproc is never in scope. We've had to go back to
the traditional top and tail method and now our reversed scripts break.

I've also tried conditionally creating the table within the recursive
sproc but again, because the table is not in existence at compile time
it causes problems: the table isn't in scope to the child sproc!

Any tricks to get around this?

Thanks in advance

Gareth


renzo.aquino Posted on 2009-05-21 01:59:16.0Z
From: renzo.aquino@gmail.com
Newsgroups: sybase.public.ase.general
Subject: Re: temp tables in recursive sprocs
Date: Wed, 20 May 2009 18:59:16 -0700 (PDT)
Organization: http://groups.google.com
Lines: 49
Message-ID: <d4a28341-329e-4950-a1cc-f98eecfc7d69@18g2000prx.googlegroups.com>
References: <4a116512$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: 203.167.117.67
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242871156 19459 127.0.0.1 (21 May 2009 01:59:16 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 21 May 2009 01:59:16 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 18g2000prx.googlegroups.com; posting-host=203.167.117.67; posting-account=tGPBogoAAADskRZsAjqsJ5xZWzZ8rxax
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/3.0.10,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!18g2000prx.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27748
Article PK: 77033


On May 18, 10:50 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
> Ran into an annoying problem and am hoping some of you may have some advice.
>
> We have a recursive legacy sproc (for tree traversal) that stores data
> in a temp table created by various sprocs that call it.
>
> The usual method to create the sproc (creating the table so it's in
> scope and deleting it after again) is not suitable because we reverse
> out a file to build all sprocs from a standard DB and of course this
> topping and tailing code isn't in syscomments, so the build file won't run.
>
> I thought I'd come up with a clever solution to this by putting this
> never-to-execute code in the recursive sproc:
>
> if 1=2
> begin
>         create #temp (blah)
> end
>
> It certainly shut the compiler up at run time, but in fact, the temp
> table of the parent sproc is never in scope.  We've had to go back to
> the traditional top and tail method and now our reversed scripts break.
>
> I've also tried conditionally creating the table within the recursive
> sproc but again, because the table is not in existence at compile time
> it causes problems: the table isn't in scope to the child sproc!
>
> Any tricks to get around this?
>
> Thanks in advance
>
> Gareth

Recursive or child sps should be created as:

create table #mysharedtable (blah) -- based on the exact fields and
types created by the callers
go
create proc mychild_or_recursive_sp
as
blah
go

-- Suggestion: You should extract sps individually or separating sps
to each file so that you don't have to drop each temp tables.

EddieA


Gareth Davies Posted on 2009-05-21 10:09:31.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: temp tables in recursive sprocs
References: <4a116512$1@forums-3-dub.sybase.com> <d4a28341-329e-4950-a1cc-f98eecfc7d69@18g2000prx.googlegroups.com>
In-Reply-To: <d4a28341-329e-4950-a1cc-f98eecfc7d69@18g2000prx.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a15285b@forums-1-dub>
Date: 21 May 2009 03:09:31 -0700
X-Trace: forums-1-dub 1242900571 10.22.241.152 (21 May 2009 03:09:31 -0700)
X-Original-Trace: 21 May 2009 03:09:31 -0700, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27750
Article PK: 77035


renzo.aquino@gmail.com wrote:
> On May 18, 10:50 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
>> Ran into an annoying problem and am hoping some of you may have some advice.
>>
>> We have a recursive legacy sproc (for tree traversal) that stores data
>> in a temp table created by various sprocs that call it.
>>
>> The usual method to create the sproc (creating the table so it's in
>> scope and deleting it after again) is not suitable because we reverse
>> out a file to build all sprocs from a standard DB and of course this
>> topping and tailing code isn't in syscomments, so the build file won't run.
>>
>> I thought I'd come up with a clever solution to this by putting this
>> never-to-execute code in the recursive sproc:
>>
>> if 1=2
>> begin
>> create #temp (blah)
>> end
>>
>> It certainly shut the compiler up at run time, but in fact, the temp
>> table of the parent sproc is never in scope. We've had to go back to
>> the traditional top and tail method and now our reversed scripts break.
>>
>> I've also tried conditionally creating the table within the recursive
>> sproc but again, because the table is not in existence at compile time
>> it causes problems: the table isn't in scope to the child sproc!
>>
>> Any tricks to get around this?
>>
>> Thanks in advance
>>
>> Gareth
>
> Recursive or child sps should be created as:
>
> create table #mysharedtable (blah) -- based on the exact fields and
> types created by the callers
> go
> create proc mychild_or_recursive_sp
> as
> blah
> go
>
> -- Suggestion: You should extract sps individually or separating sps
> to each file so that you don't have to drop each temp tables.
>
> EddieA

Thanks Eddie but I don't see how this solves the problem.


Gareth Davies Posted on 2009-05-21 10:15:15.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: temp tables in recursive sprocs
References: <4a116512$1@forums-3-dub.sybase.com> <d4a28341-329e-4950-a1cc-f98eecfc7d69@18g2000prx.googlegroups.com>
In-Reply-To: <d4a28341-329e-4950-a1cc-f98eecfc7d69@18g2000prx.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a1529b3@forums-1-dub>
Date: 21 May 2009 03:15:15 -0700
X-Trace: forums-1-dub 1242900915 10.22.241.152 (21 May 2009 03:15:15 -0700)
X-Original-Trace: 21 May 2009 03:15:15 -0700, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27751
Article PK: 77036


renzo.aquino@gmail.com wrote:
> On May 18, 10:50 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
>> Ran into an annoying problem and am hoping some of you may have some advice.
>>
>> We have a recursive legacy sproc (for tree traversal) that stores data
>> in a temp table created by various sprocs that call it.
>>
>> The usual method to create the sproc (creating the table so it's in
>> scope and deleting it after again) is not suitable because we reverse
>> out a file to build all sprocs from a standard DB and of course this
>> topping and tailing code isn't in syscomments, so the build file won't run.
>>
>> I thought I'd come up with a clever solution to this by putting this
>> never-to-execute code in the recursive sproc:
>>
>> if 1=2
>> begin
>> create #temp (blah)
>> end
>>
>> It certainly shut the compiler up at run time, but in fact, the temp
>> table of the parent sproc is never in scope. We've had to go back to
>> the traditional top and tail method and now our reversed scripts break.
>>
>> I've also tried conditionally creating the table within the recursive
>> sproc but again, because the table is not in existence at compile time
>> it causes problems: the table isn't in scope to the child sproc!
>>
>> Any tricks to get around this?
>>
>> Thanks in advance
>>
>> Gareth
>
> Recursive or child sps should be created as:
>
> create table #mysharedtable (blah) -- based on the exact fields and
> types created by the callers
> go
> create proc mychild_or_recursive_sp
> as
> blah
> go
>
> -- Suggestion: You should extract sps individually or separating sps
> to each file so that you don't have to drop each temp tables.
>
> EddieA

Thanks Eddie but I don't see how this solves the problem.


"Jeff Pryslak [Sybase]" <jeff.pryslak Posted on 2009-05-21 13:46:53.0Z
From: "Jeff Pryslak [Sybase]" <jeff.pryslak@sybase.com_nospam>
Newsgroups: sybase.public.ase.general
References: <4a116512$1@forums-3-dub.sybase.com>
Subject: Re: temp tables in recursive sprocs
Lines: 66
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a155b4d@forums-1-dub>
Date: 21 May 2009 06:46:53 -0700
X-Trace: forums-1-dub 1242913613 10.22.241.152 (21 May 2009 06:46:53 -0700)
X-Original-Trace: 21 May 2009 06:46:53 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27752
Article PK: 77037

Gareth

There are no tricks to get around this. You must create the table, as Eddie
mentioned, before the sproc will compile. There are a few tools out there
that will help with this...
Sybase WorkSpace will extract DDL,and for sproc with Temp table, will add a
create and drop table command for the proc.
The other design/dev tools, like PowerDesigner or DBArtisan can also help
here by managing the temp table definitions for generating scripts, but they
take a little tweaking to make this work.

In the end, your sproc script will have
create #temp table...
go
create sproc...
go
drop #temp table...
go
create #temp table...
go
create sproc2...
go
drop #temp table...
go

hth
jeff

"Gareth Davies" <gruff@sitemaker.cc> wrote in message
news:4a116512$1@forums-3-dub.sybase.com...
> Ran into an annoying problem and am hoping some of you may have some
> advice.
>
> We have a recursive legacy sproc (for tree traversal) that stores data in
> a temp table created by various sprocs that call it.
>
> The usual method to create the sproc (creating the table so it's in scope
> and deleting it after again) is not suitable because we reverse out a file
> to build all sprocs from a standard DB and of course this topping and
> tailing code isn't in syscomments, so the build file won't run.
>
> I thought I'd come up with a clever solution to this by putting this
> never-to-execute code in the recursive sproc:
>
> if 1=2
> begin
> create #temp (blah)
> end
>
>
> It certainly shut the compiler up at run time, but in fact, the temp table
> of the parent sproc is never in scope. We've had to go back to the
> traditional top and tail method and now our reversed scripts break.
>
> I've also tried conditionally creating the table within the recursive
> sproc but again, because the table is not in existence at compile time it
> causes problems: the table isn't in scope to the child sproc!
>
> Any tricks to get around this?
>
> Thanks in advance
>
> Gareth