Store Procedure Output in a Table

Question

Post reply

Store Procedure Output in a Table

Vinay Kumar

SSCertifiable

Points: 6099
More actions
December 18, 2009 at 12:32 pm

#215042

Comments posted to this topic are about the item Store Procedure Output in a Table
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

thanks for the query... but both the techniques fail for sp_spaceused

in the stored proc. i added something like...

CREATE PROCEDURE dbo.Tmp_SPGetAddress

AS

begin

exec sp_spaceused

end

GO

i am able to execute the above stored proc successfully... but when i use the same in the 2 techniques, it fails...

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 2

sp_spaceused returns 2 datasets with different shapes. If I'm reading BOL correctly (I haven't tested it), then in this case, INSERT INTO tries to insert both datasets into the temp table.

BOL -

If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You forgot to note that INSERT/EXEC cannot be nested. If the procedure does internally the same trick, you won't be able to insert the results into a table.

One possible solution is setting up a "SELF" linked server, pointing to the instance itself, then querying the procedure with something like:

SELECT * INTO #SomeTempTable FROM OPENQUERY(SELF,'EXEC myDB.dbo.MyProcedure')

-- Gianluca Sartori

JMasciantoni SSC Enthusiast Points: 172 More actions · Answer 4

I may be missing something on this but the solution seems unnecessarily complex. I generally use the SELECT / INTO method to allow the proc to automatically create the #temptable from the result of the SELECT (plus any joins involved). Then in the following statements I can update/delete/etc. various records as needed, finally returning the results to the calling program or wrtiting them to a fixed table. I've only come across one or two instances where it was necessary to define the #temptable first, generally when I need an IDENTITY field. Your thoughts?

John Masciantoni, Florida Health Care Plans, Florida.

codyford Grasshopper Points: 21 More actions · Answer 5

My experience is the same. Select into a temp is the technique I use unless I need a strictly structured table.

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 6

And here is a link to another way using OPENROWSET:

http://blogs.technet.com/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

david.slabyhoud Valued Member Points: 73 More actions · Answer 7

How to do nested INSERT EXEC and OUTPUT procedures data into table

Select a.* Into #TableX From OpenRowset('SQLNCLI',

'Server=servername;Trusted_Connection=yes',

'SET FMTONLY OFF; Exec procedure with nested insert exec') As a

Hope Helpful

Store Procedure Output in a Table

Cookies on SQLServerCentral