Handling Multi Record Sets from SP
Case Scenario
We have a Stored procedure and the stored procedure have two select statement on it and return different result sets. We want to store the first result set in a temporarily table and work with it. It looks interesting.
But here we mentioned the First Result Set. Not the second result set. To play with any result sets we must go to the CLR options. But with first record sets it little bit easy.
Let’s Take an Example
Step-1 [ Base Table ]
We have two base table mentioned here
SELECT * FROM MYTbl_Employee;
ID EmpName GERADE
1 Joydeep Das NULL
3 Rajesh Das NULL
8 Rani Mukharjee NULL
SELECT * FROM tbl_EMPDTLREC;
EMPID EMPNAME GRADE SAL
101 Joydeep Das A 5000
102 Chandan Bannerjee A 6000
103 Sukamal Jana B 2000
104 Rabi Mukharjee A 4000
105 Madhurima Das C 1000
Step-2 [ Now Crete a Stored Procedure with two Table ]
IF OBJECT_ID(N'dbo.proc_abc', N'P')IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[proc_abc];
END
GO
CREATE PROCEDURE [dbo].[proc_abc]
AS
BEGIN
SELECT * FROM MYTbl_Employee;
SELECT * FROM tbl_EMPDTLREC;
END
Step-3 [ Now Create a Temp Table For First Result Set ]
CREATE TABLE #temp_Output
(ID INT, EmpName VARCHAR(50), GERADE CHAR(1));
Step-4 [ Now try to Insert records Directly into Temp Table ]
INSERT INTO #temp_Output
EXEC proc_abc
Its gives an Error
Msg 213, Level 16, State 7, Procedure proc_abc, Line 6
Column name or number of supplied values does not match table definition.
Step-5 [ Now try it with OPENROWSET() ]
INSERT INTO #temp_Output
SELECT a.*
FROM
OPENROWSET
('SQLOLEDB',
'SERVER=JOYDEEP-LAPTOP\JOYDEEPSQL12;Trusted_Connection=yes;',
'set fmtonly off;
exec PRACTICE_DB.dbo.proc_abc')AS a
Step-6 [ Observation ]
SELECT * FROM #temp_Output;
ID EmpName GERADE
1 Joydeep Das NULL
3 Rajesh Das NULL
8 Rani Mukharjee NULL
Please note that, if have you any alternate options please make a comments. Please do not modify the stored procedure for that.
