Post reply

Store Procedure Output in a Table

  • 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 !!!

  • January 8, 2010 at 2:38 am

    #1100755

    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

    #1100763

    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

    #1100766

    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

    #1100811

    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

    #1101212

    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

    #1101253

    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

  • January 22, 2010 at 2:29 am

    #1107178

    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