SQL Server Performance

Working with result of stored procedure

Discussion in 'General Developer Questions' started by lipavsky, May 20, 2004.

  1. lipavsky New Member

    Hi

    I've got a following problem:

    Is it possible to work with result of stored procedure? I need to store result of stored procedure into table or cursor,... But the problem is that I don't know in advance what is the structure of the result

    for example:
    stored procedure 1 (sp1) returns: ID int, name nvarchar(100)
    stored procedure 2 (sp2) returns: name nvarchar(10), description nvarchar(10), price money

    And I need something that wil create a table/cursor/... and fill it with a result of stored procedure, but I don't know which procedure should it be (and it could be ANY stored procedure that returns one resultset)

    something like CREATE TABLE FOR EXEC ('name_of_sp')

    Is there any way to do this?

    Thanks
    Lukas
  2. Raulie New Member

    I'm puzzled on what you want to accomplish, but if you want to hold a result set you can use Temp tables.
    Here is a small excerpt form BOL on TempTables:

    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
  3. lipavsky New Member

    You misunderstood me. My problem is that I need to create table for fetching result (recordset) of stored procedure. But my problem is that I don't know the structure of the table (I don't know how many columns will procedure return and what types these columns will be).

    In my prewious example:
    I need to create procedure (save_result) that will have one param-name of another stored procedure (sp)

    save_result should execute sp and store sesults in new table (generated name and column structure depends on result od sp)

    My problem is how can I create the table of adequate structure and store the results of sp in it

    thanks
    Lukas
  4. gaurav_bindlish New Member

    I have seen this working but the table structure was known...
    INSERT INTO #TempTable
    EXEC sp_Stored_Procedure
    See if this works...
    SELECT INTO #TempTable
    EXEC sp_Stored_Procedure

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  5. bambola New Member

    It's a sort of a twisted way that would probably suffer performance-wise.

    Create a linked server. LINKED_SERVER

    Create a procedure say PUBS.dbo.Sproc1

    SELECT *
    INTO #temp
    FROM OPENQUERY(LINKED_SERVER, 'EXEC PUBS.dbo.Sproc1')


    Got the idea from Nathan (vbkenya). Here is the original post
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1001





Share This Page