Dynamically create temp table based on resultset from SP

Question

I have a SP that calls another SP and the resultset needs to be filtered to only the columns that I am interested in.

DECLARE @myTable TABLE
(
  Field1 INT,
  Field2 INT,
  Field3 INT
)

--If someSP returns say 30 params and I need only 3 params, I don't want to declare all 30 in my temp table @myTable

INSERT INTO @myTable
(Field1, Field2, Field3)   
EXEC someSP          --Need to selectively filter recordset from SP
  @InputParam1 = 'test'

If I cannot do this, I would want to create the temp table DYNAMICALLY based on the resultset from someSP (This way it relieves maintenance issues when someSP is modified to add a new param, I dont need to modify this proc as well

Answer 1

A table variable cannot be the target of a result set from another stored procedure, also you can't perform DDL on table variables after they are declared, they will always have the same definition they were declared with. Temp table is your best bet.

Answer 2

cmsjr stated, "A table variable cannot be the target of a result set from another stored procedure."

I thought that was true too, but then I tested it. This code works in both 2005 and 2008:

CREATE PROCEDURE someSP (@InputParam1 varchar(100)) AS
SELECT LEN(@InputParam1), DATALENGTH(@InputParam1), @@SPID
GO

DECLARE @myTable TABLE (
    Field1 INT,
    Field2 INT,
    Field3 INT
)

INSERT INTO @myTable (Field1, Field2, Field3)   
EXEC someSP
    @InputParam1 = 'test'

SELECT * FROM @myTable

I knew that would work with #temp tables, but I thought it would not work with @temp tables.

That doesn't answer DotnetDude's question though.

Answer 3

I could think of two options, but I didn't have time to test them: convert the SP into an User Defined Function and use the SELECT * FROM {function} INTO {table}, or use OPENROWSET:

SELECT *
FROM OPENROWSET('SQLOLEDB',
                'servername';'username';'password',
                'exec dbname.{owner}.yourstoredproc') AS spResult
INTO {tablename}

Both solutions should create the table on the fly, then you can simply select from it.

Answer 4

Based on the comments above, I'd suggest you consider a table valued function. This can be parameterised and you can do this:

INSERT @foo (col1, col14, col29)
SELECT col1, col14, col29 FROM dbo.ufnTVF (@p1, @p2)

Otherwise, it's OPENROWSET as the "cleanest" (I use this loosely) solution

Or, you modify the resultset of your stored proc to onlky return the columns you want. This implies dynamic SQL or lots of IF statements. Which in some circumstances will not parse correctly (with SET FMTONLY etc).

You could be trying to code against a 3rd party app or system stored procs (we don't have full details), but it feels messy and wrong. SQL Server 2005 has a huge number of DMVs and catalogue (or catalog depending on which side of the Atlantic you are) views that remove the need for system proc calls.

If you're trying to mimic some aspects of OO design (one proc to do something for everyboy), then I wouldn't. If you need a query that retruns 3 columns of 30, then do so. This will run far better because unused tables and columns will be ignored on in the plan, indeed do not need included.

Answer 5

Short answer: no, you can't do that.

You have to pre-declare your temp table with the exact number of columns that will be returned from the stored proc.

The workaround is to use persistent tables. For example, you could have a permanent table in your database called someSPResults. Whenever someSP is changed to have a different number of output columns, change the format of someSPResults as part of the deployment.

Then you can either do this:

insert into dbo.someSPresults
exec someSP

Or inside someSP, you can have the results be inserted directly into the someSPresults table as a normal part of execution. You just have to make sure to identify exactly which records in the someSPresults table came from each execution of someSP, because that stored proc could be fired multiple times simultaneously, thereby dumping a lot of data into someSPresults.