SQL Server Performance

How to save result returned by system procedure?

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Jun 22, 2006.

  1. pcsql New Member

    I hope someone can refresh my memory how to save result returned by system stored procedure. I cannot remember whether it is possible to save it to a variable directly.

    I only remember that I can do this:

    1. create a table which has structure same as the result set returned by a system stored procedure
    2. issue "insert into #temp exec sp_helpdb" -- using sp_helpdb as an example


    Thanks,

    Peter
  2. spacemonkey New Member

    In SQL 2000 at least, you can use the optional OUTPUT keyword after you declare the procedure variable. Do you just want to save it for use just outside the procedure or for a longer time period?

    John
  3. pcsql New Member

    Hi John,

    I'm talking about system stored procedures not stored procedures created by me.


    Peter
  4. FrankKalis Moderator

    The only allowed syntax is INSERT INTO &lt;table&gt; EXEC &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rocedure&gt; There is no way to use a variable to catch the returned resultset.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  5. Madhivanan Moderator

    Also, you cant use Insert into #t EXEC sp if sp returns more than one resultset

    Madhivanan

    Failing to plan is Planning to fail

Share This Page