How can I store a single result set in the temp table from a SQL stored procedure returning multiple sets?
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 understand: "Insert error: Column name or number of values specified does not match the table definition."
Examining the procedure (which I cannot edit) shows the following:
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
Thus, it actually repeats the parameters passed to it as the second result set. (I have no idea why, I would understand that everything that calls the procedure will know what data they were passing in.)
My testing leads me to think that the second result set is what is causing the insert to fail - for example SQL trying to merge the result sets together and fail.
I only want the first result set stored in my temporary table. How can i do this?
Edit
Thanks for pointing out the programs stored in the CLR, but this feature was introduced in SQL 2005 - it won't work in 2000. (But I didn't know about this before, and they look like they'll be useful when we update.)
As soon as the other answers seem to be "you can't," I think it will come back to the drawing board for me.
source to share
since you cannot change the stored procedure, you only have two options:
- use a CLR that can capture both result sets and only return the one you want.
- duplicate the request you need in your own procedure or view. This is a real hack and the CLR is preferred. However, you have little choice.
source to share
This is generally not possible in plain SQL. Brannon's link gives a possible workaround using the CLR.
On the other hand, if refactoring is an option, consider making the first request an atom in your own stored procedure. It can then be called from either an existing stored procedure or from any other code. Your code is still only in one place, nothing is broken, and you end up with something that 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 a table-oriented inline function (I've found them to work well and are flexible). Then you don't even need to capture the output into a temporary table in order to use it as a table when doing other processing (although you might want to if you want to use it multiple times)!
source to share
Below is a complete, working (for SQL 2005) example of what you are talking about.
Bad news: I don't believe there is a way to do what you are trying to do. I'm sorry. It looks like the SP author made this impossible.
If anyone 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.
source to share
From Microsoft T-SQL Reference
INSERT EmployeeSales
EXECUTE uspGetEmployeeSales;
GO
source to share