insert into using exec proc

[Ron]

I get an error executing this statement

create table #tempTable(col1 char(10))
insert into #temp1 (col1)
exec another_proc

Incorrect syntax near the word 'exec'.
This works in MS SQL Server.
Is syntax wrong or I just can't insert into table using
stored proc in Sybase?
Can someone suggest an alternative solution for using
another stored proc output in my stored proc? "another_proc"
returns multiple rows.

Thank you.

[Mark A. Parsons]

You can't do this with Sybase/ASE as proposed.

You have a couple options:

1 - perform the insert from within the stored proc

or

2 - consider setting up a proxy table against the stored proc, then insert into #temp1 from the proxy table; see the
'create existing table' command in the Reference manuals; you'll have to make sure you have a dummy/loopback server
defined so that you can fake-out ASE into thinking the procedure is in a remote ASE; eg:

============================
-- assuming your ASE's @@servername has been defined

sp_addserver loopback,@@servername
go

use tempdb
go

-- assume proc created by 'dbo'

create proc p1
as
select id from sysobjects
go

-- sysobjects.id is of type 'int'

create existing table
p1_tab
(a int) -- column list must match # and type
-- of proc's result set, but names do not
-- need to match for result set columns;
-- not verified by 'create existing table'
external procedure
at 'loopback.tempdb.dbo.p1'
go

select * from p1_tab
go

a
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
21
22
23
24
25
26
27
28
29
98
544001938
560001995
785901932
1217903471

============================

See the Reference manuals for more instructions on how to pass input parameters to the stored proc (if required).