Capturing stored procedure output into a table

  • Hi all

    I would like to use the output from a stored procedure and put it in a table. So far I've not determined how to do this.

    I know that I can nest a select statement inside a create table statement in order to generate the column data types and populate the rows without having to define the column types explicitly.

    Is there a way to do it similarly (either create a table or view) for the output of a stored procedure which returns rows of data?

    I hope that my question is clear.

    Thanks!

  • Is this what you mean?

    create proc proca as

    select column1, column2

    from table_a

    go

    create proc procb as as

    create table #temp

    (column1 varchar(50), column2 varchar(50))

    insert #temp

    exec proca

    go

    Just be aware that you cannot nest insert/exec procedures. You might be able to get around this using table variables (if you have SQL2000) but I have not tried it.

    Jeremy

  • quote:


    You might be able to get around this using table variables (if you have SQL2000) but I have not tried it.


    You can't use exec on a table variable 🙁

    Only a normal table

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • will this work?

    create table #tmp (

    myfieldname VARCHAR(100)

    )

    insert #tmp

    exec('usp_build_list')

  • this would work.

    select * from openquery (servername,'exec dbname.dbo.spname')

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply