December 18, 2009 at 12:32 pm
Comments posted to this topic are about the item Store Procedure Output in a Table
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
January 8, 2010 at 2:38 am
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...
January 8, 2010 at 3:22 am
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
January 8, 2010 at 3:37 am
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
January 8, 2010 at 6:31 am
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.
January 8, 2010 at 2:49 pm
My experience is the same. Select into a temp is the technique I use unless I need a strictly structured table.
January 8, 2010 at 4:22 pm
And here is a link to another way using OPENROWSET:
January 22, 2010 at 2:29 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy