How can I get data from a stored procedure into a temp table ?

Question

Am working on sybase ASE 15. Looking for something like this

Select * into #tmp exec my_stp;

my_stp returns 10 data rows with two columns in each row.

Answer 1

Not sure about Sybase, but in SQL Server the following should work:

INSERT INTO #tmp (col1,col2,col3...) exec my_stp

Answer 2

You don't state whether you're stored procedure returns more than one value. If it only returns one value then you can do the following:

--create store procedure
create procedure sp_returnOne
as 
 BEGIN
 return 1
END

--create temp table
create table #tt (
    col1 int null
)

--populate temp table #tt with stored procedure

declare @result int
exec @result =  sp_returnOne
insert into #tt values (@result)

select * from #tt

drop table #tt

---

If your sp returns more than value then you'll have to define the output variables in the sp. From the sybase manual http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/44105;pt=44072

"A stored procedure can return several values; each must be defined as an output variable in the stored procedure and in the calling statements. The output keyword can be abbreviated to out. exec myproc @a = @myvara out, @b = @myvarb out"

You don't say what version of sybase you are working on. All of the above is valid for ASE 12.5.

Answer 3

In Sybase SQL Anywhere,

INSERT INTO #tmp (col1,col2,col3...) select * from my_stp()

Answer 4

In ASE 15 I believe you can use functions, but they're not going to help with multirow datasets.

If your stored proc is returning data with a "select col1,col2 from somewhere" then there's no way of grabbing that data, it just flows back to the client.

What you can do is insert the data directly into the temp table. This can be a little tricky as if you create the temp table within the sproc it is deleted once the sproc finishes running and you don't get to see the contents. The trick for this is to create the temp table outside of the sproc, but to reference it from the sproc. The hard bit here is that every time you recreate the sproc you must create the temp table, or you'll get "table not found" errors.

    --You must use this whole script to recreate the sproc    
    create table #mine
    (col1 varchar(3),
    col2 varchar(3))
    go
    create procedure my_stp
    as
    insert into #mine values("aaa","aaa")
    insert into #mine values("bbb","bbb")
    insert into #mine values("ccc","ccc")
    insert into #mine values("ccc","ccc")
    go
    drop table #mine
    go

The to run the code:

create table #mine
(col1 varchar(3),
col2 varchar(3))
go

exec my_stp
go

select * from #mine
drop table #mine
go