Splitting individual stored procedures into temporary tables
I have a stored procedure that returns 2 result sets. I am trying to call a stored procedure and insert the values of the first result set into a temporary table, but I cannot do this because I am returning 2 result sets.
Is there a way to return only one result set or both of them in separate temporary tables. I cannot change the stored procedure.
Stored Procedure Result Set 1
column a | column b | coulmn c
Stored Procedure Result Set 2
column x | column y
What i do
DECLARE @ResultSet1Table
TABLE (
column a
,column b
,column c
)
INSERT INTO @ResultSet1Table
EXEC StoredProc
And getting an error message
Column name or number of values specified does not match table definition
because of the second result set.
source to share
Ok, this is a bit of a hack:
CREATE PROCEDURE SPMultipleResultsSets
AS
SELECT *
FROM
( VALUES (1),(2),(3),(4)) Vals(Num)
SELECT *
FROM
( VALUES ('ABC'),('DEF'),('GHI'),('JKL')) Strings(string)
You need to enable special distributed queries:
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #Temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC DBNAME.dbo.SPMultipleResultsSets')
-- Select Table
SELECT *
FROM #Temp;
Return:
Num
1
2
3
4
source to share
I think I have a reasonable job. Just add columns to identify each result set. Then separate them with queries. Check it:
CREATE PROCEDURE usp_test
AS
SELECT colA = 'A',
colB = 'B',
colC = 'C';
SELECT colX = 'X',
colY = 'Y',
'!';
GO
DECLARE @ResultSetTable TABLE(col1 CHAR,col2 CHAR,col3 CHAR);
INSERT INTO @ResultSetTable
EXEC usp_test
--Set 1
SELECT *
FROM @ResultSetTable
WHERE col3 <> '!'
--Set 2
SELECT *
FROM @ResultSetTable
WHERE col3 ='!'
Results for Set 1:
col1 col2 col3
---- ---- ----
A B C
Results for Set 2:
col1 col2 col3
---- ---- ----
X Y !
source to share