Post reply

Return the results of a SP into a view\table\temp table

  • August 23, 2007 at 3:04 am

    #68923

    Hi,

    I'm experiencing a bit of a problem.

    I have a number of simple stored procedures that just return some rows of data.

    I am trying to store the results of the SP into a structure that I can query.

    The problem is that I dont know at run-time which SP will be executed OR what columns the SP returns.

    I was trying to use temp tables but couldnt because I dont know the structure of the data returned from the SP.

    I also thought about using VIEWS.  IE

    CREATE VIEW MyTest AS EXEC MySP

    but apparently this cant be done !

    Any help would be greatly appreciated.

    Thanks

  • August 23, 2007 at 3:17 am

    #728823

    You can dynamically create and copy a table using

    select *

    into new_table

    from some_other_table

    Thus have you tried

    select *

    into new_table

    EXEC MySP

  • August 23, 2007 at 3:34 am

    #728829

    that would be

     

    insert table1

    exec mysp1

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 23, 2007 at 3:42 am

    #728832

    Gents,

    Thanks for your replies.

    However, i'm still a little confused.

    insert table1

    exec mysp1

    I get the following error: Invalid object name 'table1'.

    I'm assuming that this is because I havent declared or created table1 ?

    Thanks

    Stuart

     

  • August 23, 2007 at 4:05 am

    #728838

    Stuart

    That's right - you need to create the table first.

    Probably the most elegant way of achieving your goal would be to edit your stored procedures so that they automatically write to the table.  If you don't want it to do this every time the procedure is run, then add another parameter @WriteToTable, for example, which determines whether this will be done.

    If you can't amend your stored procedures, then I think dynamic SQL is your only option.  How about creating a master table that holds the table definition for the result set of each proc?  Then you can dynamically create the table and put the output of the stored procedure into it.  Or you could create one table for each proc in advance and just store the procedure name and the table name in it.

    John

  • August 23, 2007 at 4:15 am

    #728841

    John,

    Thanks for your reply and your suggestions

    I'm not sure that I can modify the SP's directly. 

    However, I am interested in the dynamic SQL approach.  You stated 'How about creating a master table that holds the table definition for the result set of each proc?'

    I think I understand where you are coming from.  However, I dont know in advance what the resulset of the Sp is until it's run.  (Basically i call a sp with the name of the SP I want to run as a parmater and then run the SP passed into the proc and store the results inot a table)

    So I may have something like

    create proc MyProc @sql varchar AS

    -- exec SQL into a table !!!!

    Regards

    Stuart

  • August 23, 2007 at 4:32 am

    #728843

    Just a thought - Is there a system sp that returns the col definitions for a given stored procedure?

  • August 23, 2007 at 4:36 am

    #728845

    Stuart

    Can you post an example of one of the SPs that you run?  If each one always produces the same columns in the result set, then my suggestion will work.  If you have dynamic SQL in your proc that causes different results sets each time, then your solution is going to be very messy.  It may be simpler (and better for your long-term sanity) in that case to redesign your system so that dynamic SQL is eliminated and stored procedures and tables are not chosen on the fly.

    John

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

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