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

TempDB for Performance Part-II

Introduction

One of my article is related to TempDB for Performance, published on 28^th Feb 2015. Those who are not read it yet can read it from

http://sqlknowledgebank.blogspot.in/2015/02/tempdb-for-performance.html

Now the question came to mid how to change the location of the TempDB from current location to other drive. Here in this article, I am providing the T-SQL command for that. We are not going to discuss about the performance related factors of TempDB. As you can find it form our previous article mentioned above.

In Which Drive My TempDB is Currently Located

USE tempdb

GO

EXEC sp_helpfile;

So we find that the MDF and LDF file of the temp DB is located on

C:\Program Files\Microsoft SQL Server\MSSQL11.JOYDEEPSQL12\MSSQL\DATA

How We Move the TempDB to Another Drive

Suppose we want to move the TempDB from current location to E:\TEMPDB\

USE MASTER

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = tempdev, FILENAME = 'E:\TEMPDB\tempdb.mdf')

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = templog, FILENAME = 'E:\TEMPDB\templog.ldf')

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS

PAGEDATA T-SQL in SQL 2012

Introduction

In this article we are trying to discuss about a new feature of Microsoft SQL server called page data. Hope you will find it informative.

What the Page Data is

When we are taking about front-end grid to display data we have limitation of spaces, so we represent it via paging in the grid. The paging system is a feature of grid that we used in our front-end. But when we move throw pages we fired the SQL Statement to retrieve data from our database. Suppose our grid can display five data at a time, so we retrieve 1 to 5 records first from database and when the user click on the next page we retrieve records from 6 to 10 and so on.

How We do it

Step-1 [ Create the Base Table  ]

IF OBJECT_ID(N'dbo.tbl_ItemMast', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_ItemMast];

   END

GO

CREATE TABLE [dbo].[tbl_ItemMast]

   (

      ITEMCD      BIGINT        NOT NULL PRIMARY KEY,

        ITEMNAME    VARCHAR(50)   NOT NULL

   );

GO

Step-2 [ Insert Some Records in our Base Table ]

INSERT INTO [dbo].[tbl_ItemMast]

       (ITEMCD, ITEMNAME)

VALUES (1, 'Tooth Paste'), (2, 'Tooth Brush'),

       (3, 'Banana'), (4, 'Apple'), (5, 'Orange'),

       (6, 'Saving Lootion'), (7, 'Oil'), (8, 'Saving Cream'),

       (9, 'Cake'), (10, 'Rice');

GO

Step-3 [ How we Retrieve Records Before MS SQL Server 2012 ]

SELECT a.*

FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME

        FROM   [dbo].[tbl_ItemMast])AS a

WHERE  a.RNUM >=1 AND a.RNUM<=5;

Output :

SELECT a.*

FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME

        FROM   [dbo].[tbl_ItemMast])AS a

WHERE  a.RNUM >=5 AND a.RNUM<=10;

Output:

Step-4 [ How we retrieve records in SQL Server 2012 ]

SELECT *

FROM   [dbo].[tbl_ItemMast]

ORDER BY ITEMCD

OFFSET 0 ROWS

FETCH NEXT 5 ROWS ONLY;

Output:

SELECT *

FROM   [dbo].[tbl_ItemMast]

ORDER BY ITEMCD

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY;

Output:

Hope you like it.

Posted by: MR. JOYDEEP DAS

T-SQL ROLLUP and CUBE

Introduction

First of all I salute all the new and reward MVP in the month of April 2015. Congratulating them for their achievement. Hope the community can learn a lot of new thing from them.

After my blog related to “Group By Grouping Set” request came from my friends circle to completing the article by providing something related to CUBE, ROLLUP and COMPUTE. So in this article we are trying to learn something related to it. Hope it will be informative.

CUBE and ROLLUP

Generally CUBE and ROLLUP is used in reporting purpose and they do the Subtotal and Grand total. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns and ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let’s take an Example to understand it

We have a table like this.

Class	Section	Roll	Marks
1	A	1	40
1	A	2	30
1	A	3	20
1	B	1	40
1	B	2	30
1	B	3	30
2	A	1	20
2	A	2	60
2	A	3	40
2	B	1	20
2	B	2	30
2	B	3	20

If we make the WITH ROLLUP we can get the Output like this

Class	Section	Marks
1	A	90
1	B	100
1	NULL	190	90 + 100
2	A	120
2	B	70
2	NULL	190	120 + 70
NULL	NULL	380	190 + 190

If we make the WITH CUBE we can get the Output like this

Class	Section	Marks
1	A	90
1	B	100
1	NULL	190	90 + 100
2	A	120
2	B	70
2	NULL	190	120 + 70
NULL	NULL	380	190 + 190
NULL	A	210	90   +  120
NULL	B	170	100 +  70

Let’s take a practical example

Step- 1 [ Create Base Table ]

IF OBJECT_ID(N'[dbo].[tbl_EXAMPLETABLE]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EXAMPLETABLE];

   END

GO

CREATE TABLE [dbo].[tbl_EXAMPLETABLE]

    (

        CLASS        INT       NOT NULL,

        SECTION      CHAR(1)   NOT NULL,

        ROLL         INT       NOT NULL,

        MARKS        INT       NOT NULL

   );   

Step-2 [ Insert Records in Base Table ]

INSERT INTO [dbo].[tbl_EXAMPLETABLE]

    (CLASS, SECTION, ROLL, MARKS)

VALUES(1,  'A', 1, 40),

      (1,  'A',   2, 30),

      (1,  'A',   3, 20),

      (1,  'B',   1, 40),

      (1,  'B',   2, 30),

      (1,  'B',   3, 30),

      (2,  'A',   1, 20),

      (2,  'A',   2, 60),

      (2,  'A',   3, 40),

      (2,  'B',   1, 20),

      (2,  'B',   2, 30),

      (2,  'B',   3, 20);

Step – 4 [ Make the WITH ROLLUP ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY CLASS, SECTION WITH ROLLUP;

Output

Step-5 [ Make the WITH CUBE  ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY CLASS, SECTION WITH CUBE;

Output

Hope you like it.

Posted by: MR. JOYDEEP DAS