sap ase - 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

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