Saturday 18 April 2015

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

3 comments:

  1. As you mentioned if both the result set are different in nature like number of rows, non compatible datatypes then only we faced this challenges . Other wise if the Datatype and the result set are same then both the result set will get inserted into the same tables.

    alter proc test
    as
    begin
    select top 10 name from sys.indexes
    select top 10 name from sys.columns
    end


    create table t (name varchar(100))

    truncate table t

    insert into t
    exec test

    ReplyDelete
  2. Thank you Joydeep Das, Its interesting..

    ReplyDelete
    Replies
    1. Thanks @ Vikas
      In my next post i am trying to post the CLR option of that.

      Delete