How to save a single result set to a temp table from a SQL stored procedure returning multiple sets?

Question

I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work:

create table #tempTable (TempId int primary key, Column1 varchar(100), 
Column2 varchar(100), DateCreated datetime)

insert into #tempTable (TempId, Column1, Column2, DateCreated)
exec sproc_Select_Stuff_By_DateCreated @Date1 = '1/1/2009', @Date2 = '1/2/2009'

But I get: "Insert Error: Column name or number of supplied values does not match table definition."

Examining the procedure (which I cannot edit) reveals this:

CREATE PROCEDURE sproc_Select_Stuff_By_DateCreated

@Date1 datetime,
@Date2 datetime
AS

BEGIN

SELECT TempId, Column1, Column2, DateCreated
FROM ReallyHugeMessOfJoinsAndCalculatedColumns
WHERE DateCreated between @Date1 and @Date2

SELECT @Date1 as Date1, @Date2 as Date2

END

So it's actually echoing back the parameters passed into it as a second result set. (I have no idea why; I'd figure that anything calling the procedure would know what data it was passing in.)

My testing leads me to think that the second result set is what's causing the insert failure - like SQL is trying to union the result sets together and failing.

I only need the first result set saved to my temporary table. How can I do that?

Edit

Thanks for pointing out CLR stored proecedures, but that feature was introduced in SQL 2005 - it won't work for 2000. (But I wasn't previously aware of them, and they look like they'll be useful when we upgrade.)

As the only other answers look to be "you can't" - I guess it's back to the drawing board for me.

Answer 1

Below is a complete, working (for SQL 2005) example of what you're talking about.

The bad news is, I don't believe there is any way to do what you're trying to do. Sorry. It looks like the SP writer made it impossible.

If someone comes up with some creative way to make this work, great!

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
    DROP TABLE #tempTable
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'sproc_Select_Stuff_By_DateCreated')
    DROP PROCEDURE dbo.sproc_Select_Stuff_By_DateCreated
GO
CREATE PROCEDURE dbo.sproc_Select_Stuff_By_DateCreated
    @Date1 datetime,
    @Date2 datetime
AS BEGIN
    ;WITH t AS (
        SELECT
            1                       AS TempId,
            'Column1-val1'          AS Column1,
            'Column2-val1'          AS Column2,
            '2009-01-01 10:00:00'   AS DateCreated
        UNION ALL
        SELECT
            2,
            'Column1-val2',
            'Column2-val2',
            '2009-01-01 11:00:00'
    )
    SELECT
        TempId,
        Column1,
        Column2,
        DateCreated
    FROM t -- ReallyHugeMessOfJoinsAndCalculatedColumns
    WHERE DateCreated between @Date1 and @Date2

    SELECT @Date1 as Date1, @Date2 as Date2
END
GO

create table #tempTable (
    TempId int primary key,
    Column1 varchar(100),
    Column2 varchar(100),
    DateCreated datetime
)

insert into #tempTable (TempId, Column1, Column2, DateCreated)
exec dbo.sproc_Select_Stuff_By_DateCreated
    @Date1 = '1/1/2009',
    @Date2 = '1/2/2009'

--SELECT * FROM #tempTable

----------------------------------------

Msg 213, Level 16, State 7, Procedure sproc_Select_Stuff_By_DateCreated, Line 26
Insert Error: Column name or number of supplied values does not match table definition.

Answer 2

See this related question: http://stackoverflow.com/questions/58940/access-to-result-sets-from-within-stored-procedures-transact-sql-sql-server

Answer 3

Generally, in plain SQL this is not possible. Brannon's link gives a possible workaround using the CLR.

On the other hand, if re-factoring is an option, think about making the first query atomic in it's own stored procedure. Then it can be called from both the existing stored procedure and any other code. Your code is still only in one place, nothing is broken, and you get something which can be more easily used from pure SQL. Depending on its function, the first part of the SP might even be a good candidate for an inline table-valued function (I've found these to be well-performant and flexible). Then you don't even need to capture the output into a temp table to use it as a table in doing other processing (although you might want to if you want to use it several times)!

Answer 4

since you can't change the stored procedure, you have only two options:

• use a CLR, that can capture both result sets, and return only the one you want.
• duplicate the query you need in your own procedure or view. It is a real hack, and the CLR is preferred. However, you don't have many choices.

Answer 5

From the Microsoft T-SQL reference

INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO