SQL Server Performance Forum – Threads Archive

How to save result returned by system procedure?

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

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

Hi John, I’m talking about system stored procedures not stored procedures created by me.
Peter

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

Also, you cant use Insert into #t EXEC sp if sp returns more than one resultset Madhivanan Failing to plan is Planning to fail

]]>