Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Store Procedure Output in a Table Expand / Collapse
Author
Message
Posted Friday, December 18, 2009 12:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Comments posted to this topic are about the item Store Procedure Output in a Table



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #836580
Posted Friday, January 8, 2010 2:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,061, Visits: 370
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...
Post #844169
Posted Friday, January 8, 2010 3:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 1,255, Visits: 2,230
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
Posted Friday, January 8, 2010 3:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 4,396, Visits: 10,684
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #844185
Posted Friday, January 8, 2010 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 8, 2010 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 8, 2010 4:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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
Posted Friday, January 22, 2010 2:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 »

Add to briefcase

Permissions Expand / Collapse