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.

Hope you like it.

Posted by: MR. JOYDEEP DAS