Working with result of stored procedure

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

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.

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

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.

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