Store Procedure Output in a Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by vinaykumar » Store Procedure Output in a Table

Store Procedure Output in a Table

Rate Topic

Display Mode

Topic Options

Author

Message

Danny Ocean

Posted Friday, December 18, 2009 12:32 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 1,085, Visits: 1,166

Comments posted to this topic are about the item Store Procedure Output in a Table

Keep Learning - Keep Growing !!!
http://growwithsql.blogspot.in

Thanks
Vinay Kumar

Post #836580

ziangij

Posted Friday, January 08, 2010 2:38 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,530, Visits: 359

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

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...

Post #844169

Andrew Watson-478275

Posted Friday, January 08, 2010 3:22 AM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875

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

Post #844181

spaghettidba

Posted Friday, January 08, 2010 3:37 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 4,804, Visits: 8,074

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')

Get your two-cent-answer quickly
The Spaghetti DBA

Post #844185

JMasciantoni

Posted Friday, January 08, 2010 6:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 16, 2010 4:42 AM
Points: 4, Visits: 15

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.

Post #844241

codyford

Posted Friday, January 08, 2010 2:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 11:46 AM
Points: 3, Visits: 13

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

Post #844700

Adam Gojdas

Posted Friday, January 08, 2010 4:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79, Visits: 1,317

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

Post #844751

david.slabyhoud

Posted Friday, January 22, 2010 2:29 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, December 13, 2012 2:07 AM
Points: 53, Visits: 18

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

Post #851841

« Prev Topic | Next Topic »

Permissions