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.

Use stored procedure as insert source

5 posts in General Discussion Last posting was on 2010-12-09 19:49:00.0Z
Mark Posted on 2010-12-08 21:52:31.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Use stored procedure as insert source
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: <4cfffe1f$1@forums-1-dub>
Date: 8 Dec 2010 13:52:31 -0800
X-Trace: forums-1-dub 1291845151 10.22.241.152 (8 Dec 2010 13:52:31 -0800)
X-Original-Trace: 8 Dec 2010 13:52:31 -0800, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29781
Article PK: 79010

Is there a way to take the result set of a stored procedure and insert
it into a temp table inside of a stored procedure?

I'm trying to do something akin to this...

insert #tables
(
table_qualifier,
table_owner ,
table_name ,
table_type ,
remarks
)
execute sp_tables

We are using the following version: Adaptive Server
Enterprise/15.0.3/EBF 16737 ESD#2

Thanks,
Mark


Rob V [ Sybase ] Posted on 2010-12-08 22:00:05.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Use stored procedure as insert source
References: <4cfffe1f$1@forums-1-dub>
In-Reply-To: <4cfffe1f$1@forums-1-dub>
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: <4cffffe5$1@forums-1-dub>
Date: 8 Dec 2010 14:00:05 -0800
X-Trace: forums-1-dub 1291845605 10.22.241.152 (8 Dec 2010 14:00:05 -0800)
X-Original-Trace: 8 Dec 2010 14:00:05 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29782
Article PK: 79011


On 08-Dec-2010 22:52, Mark wrote:
> Is there a way to take the result set of a stored procedure and insert
> it into a temp table inside of a stored procedure?
>
> I'm trying to do something akin to this...
>
> insert #tables
> (
> table_qualifier,
> table_owner ,
> table_name ,
> table_type ,
> remarks
> )
> execute sp_tables
>
> We are using the following version: Adaptive Server
> Enterprise/15.0.3/EBF 16737 ESD#2
>
> Thanks,
> Mark

See www.sypron.nl/proctab ...

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Mark Posted on 2010-12-09 13:30:38.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Use stored procedure as insert source
References: <4cfffe1f$1@forums-1-dub> <4cffffe5$1@forums-1-dub>
In-Reply-To: <4cffffe5$1@forums-1-dub>
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: <4d00d9fe$1@forums-1-dub>
Date: 9 Dec 2010 05:30:38 -0800
X-Trace: forums-1-dub 1291901438 10.22.241.152 (9 Dec 2010 05:30:38 -0800)
X-Original-Trace: 9 Dec 2010 05:30:38 -0800, vip152.sybase.com
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29783
Article PK: 79017

Hi Rob,
Thanks for the link. I am able to run the create and select from within
SQL Advantage but can't seem to get it working when I stick them in a
proc. I get the message: "Statement with location clause must be the
only statement in a query batch." Is there a way to populate a table
using a proc inside a proc?

Here's a scaled down version of the proc I'm working on...

CREATE PROCEDURE sp_reset_keys
AS

DECLARE @li_return_code integer

select @li_return_code = 1

create existing table #tables
(
table_qualifier varchar(30) null,
table_owner varchar(30) null,
table_name varchar(255) null,
table_type varchar(12) null,
remarks int null
)
external procedure at 'loopback.secfin..sp_tables'

/* see what got inserted into the temp table */
select * from #tables

Return @li_return_code
go

grant execute on sp_reset_keys to public
go

Mark

On 12/8/2010 5:00 PM, Rob V [ Sybase ] wrote:
> On 08-Dec-2010 22:52, Mark wrote:
>> Is there a way to take the result set of a stored procedure and
>> insert it into a temp table inside of a stored procedure?
>>
>> I'm trying to do something akin to this...
>>
>> insert #tables
>> (
>> table_qualifier,
>> table_owner ,
>> table_name ,
>> table_type ,
>> remarks
>> )
>> execute sp_tables
>>
>> We are using the following version: Adaptive Server
>> Enterprise/15.0.3/EBF 16737 ESD#2
>>
>> Thanks,
>> Mark
>
> See www.sypron.nl/proctab ...
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
> -----------------------------------------------------------------
>


Bret Halford Posted on 2010-12-09 16:30:36.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Use stored procedure as insert source
References: <4cfffe1f$1@forums-1-dub> <4cffffe5$1@forums-1-dub> <4d00d9fe$1@forums-1-dub>
In-Reply-To: <4d00d9fe$1@forums-1-dub>
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: <4d01042c$3@forums-1-dub>
Date: 9 Dec 2010 08:30:36 -0800
X-Trace: forums-1-dub 1291912236 10.22.241.152 (9 Dec 2010 08:30:36 -0800)
X-Original-Trace: 9 Dec 2010 08:30:36 -0800, vip152.sybase.com
Lines: 92
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29787
Article PK: 79015

Don't create the proxy table as a temp table within the procedure.
Create it as a permanent table within the database. You can then
use it from multiple procedures, adhoc queries, etc. Proxy tables
don't use disk storage aside from some rows in the system catalogs
(i.e. no extents are allocated to their object id) so overhead
of keeping such tables around is low.

-bret

On 12/9/2010 6:30 AM, Mark wrote:
> Hi Rob,
> Thanks for the link. I am able to run the create and select from within
> SQL Advantage but can't seem to get it working when I stick them in a
> proc. I get the message: "Statement with location clause must be the
> only statement in a query batch." Is there a way to populate a table
> using a proc inside a proc?
>
> Here's a scaled down version of the proc I'm working on...
>
> CREATE PROCEDURE sp_reset_keys
> AS
>
> DECLARE @li_return_code integer
>
> select @li_return_code = 1
>
> create existing table #tables
> (
> table_qualifier varchar(30) null,
> table_owner varchar(30) null,
> table_name varchar(255) null,
> table_type varchar(12) null,
> remarks int null
> )
> external procedure at 'loopback.secfin..sp_tables'
>
> /* see what got inserted into the temp table */
> select * from #tables
>
> Return @li_return_code
> go
>
> grant execute on sp_reset_keys to public
> go
>
> Mark
>
> On 12/8/2010 5:00 PM, Rob V [ Sybase ] wrote:
>> On 08-Dec-2010 22:52, Mark wrote:
>>> Is there a way to take the result set of a stored procedure and
>>> insert it into a temp table inside of a stored procedure?
>>>
>>> I'm trying to do something akin to this...
>>>
>>> insert #tables
>>> (
>>> table_qualifier,
>>> table_owner ,
>>> table_name ,
>>> table_type ,
>>> remarks
>>> )
>>> execute sp_tables
>>>
>>> We are using the following version: Adaptive Server
>>> Enterprise/15.0.3/EBF 16737 ESD#2
>>>
>>> Thanks,
>>> Mark
>>
>> See www.sypron.nl/proctab ...
>>
>> HTH,
>>
>> Rob V.
>> -----------------------------------------------------------------
>> Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>> and Replication Server 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at www.sypron.nl/shop):
>> "Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>> -----------------------------------------------------------------
>>


Mark Posted on 2010-12-09 19:49:00.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Use stored procedure as insert source
References: <4cfffe1f$1@forums-1-dub> <4cffffe5$1@forums-1-dub> <4d00d9fe$1@forums-1-dub> <4d01042c$3@forums-1-dub>
In-Reply-To: <4d01042c$3@forums-1-dub>
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: <4d0132ac$1@forums-1-dub>
Date: 9 Dec 2010 11:49:00 -0800
X-Trace: forums-1-dub 1291924140 10.22.241.152 (9 Dec 2010 11:49:00 -0800)
X-Original-Trace: 9 Dec 2010 11:49:00 -0800, vip152.sybase.com
Lines: 95
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29792
Article PK: 79020

Thanks Bret!

On 12/9/2010 11:30 AM, Bret Halford wrote:
> Don't create the proxy table as a temp table within the procedure.
> Create it as a permanent table within the database. You can then
> use it from multiple procedures, adhoc queries, etc. Proxy tables
> don't use disk storage aside from some rows in the system catalogs
> (i.e. no extents are allocated to their object id) so overhead
> of keeping such tables around is low.
>
> -bret
>
>
> On 12/9/2010 6:30 AM, Mark wrote:
>> Hi Rob,
>> Thanks for the link. I am able to run the create and select from within
>> SQL Advantage but can't seem to get it working when I stick them in a
>> proc. I get the message: "Statement with location clause must be the
>> only statement in a query batch." Is there a way to populate a table
>> using a proc inside a proc?
>>
>> Here's a scaled down version of the proc I'm working on...
>>
>> CREATE PROCEDURE sp_reset_keys
>> AS
>>
>> DECLARE @li_return_code integer
>>
>> select @li_return_code = 1
>>
>> create existing table #tables
>> (
>> table_qualifier varchar(30) null,
>> table_owner varchar(30) null,
>> table_name varchar(255) null,
>> table_type varchar(12) null,
>> remarks int null
>> )
>> external procedure at 'loopback.secfin..sp_tables'
>>
>> /* see what got inserted into the temp table */
>> select * from #tables
>>
>> Return @li_return_code
>> go
>>
>> grant execute on sp_reset_keys to public
>> go
>>
>> Mark
>>
>> On 12/8/2010 5:00 PM, Rob V [ Sybase ] wrote:
>>> On 08-Dec-2010 22:52, Mark wrote:
>>>> Is there a way to take the result set of a stored procedure and
>>>> insert it into a temp table inside of a stored procedure?
>>>>
>>>> I'm trying to do something akin to this...
>>>>
>>>> insert #tables
>>>> (
>>>> table_qualifier,
>>>> table_owner ,
>>>> table_name ,
>>>> table_type ,
>>>> remarks
>>>> )
>>>> execute sp_tables
>>>>
>>>> We are using the following version: Adaptive Server
>>>> Enterprise/15.0.3/EBF 16737 ESD#2
>>>>
>>>> Thanks,
>>>> Mark
>>>
>>> See www.sypron.nl/proctab ...
>>>
>>> HTH,
>>>
>>> Rob V.
>>> -----------------------------------------------------------------
>>> Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>>> and Replication Server 15.0.1/12.5 // TeamSybase
>>>
>>> Author of Sybase books (order online at www.sypron.nl/shop):
>>> "Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>>> "The Complete Sybase ASE Quick Reference Guide"
>>> "The Complete Sybase Replication Server Quick Reference Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>>> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>>> -----------------------------------------------------------------
>>>
>