sp_describe_first_result_set in MS SQL 2012

Introduction 

When I am moving with MS SQL 2012 it surprised me every time. This time I have a new system stored procedure called sp_describe_first_result_set. Which come with MS SQL Server 2012. Grade thing MS provide us.

 

So want is it

 

To understand any table object or view Meta data we always used system stored procedure sp_Help

To understand it lets take an example

Step-1 [ Create Base Table ]

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

   BEGIN

      DROP TABLE   [dbo].[tbl_STUDENT];

   END

GO

CREATE TABLE [dbo].[tbl_STUDENT]

       (STDROLL      INT          NOT NULL IDENTITY PRIMARY KEY,

       STDNAME      VARCHAR(50)  NOT NULL,

       STDCLASS     INT          NOT NULL);

GO

Step-2 [ Insert Records ]

INSERT INTO [dbo].[tbl_STUDENT]

       (STDNAME, STDCLASS)

VALUES ('Joydeep Das', 1),               

       ('Rajesh Das', 1),

       ('Deepasree Das', 1),

       ('Dia Das', 1),

       ('Shipra Das', 1);

Step-3 [ Create Views ]

IF OBJECT_ID(N'dbo.view_STDDETALS', N'V') IS NOT NULL

   BEGIN

      DROP VIEW   [dbo].[view_STDDETALS];

   END

GO

CREATE VIEW [dbo].[view_STDDETALS]

AS

SELECT STDROLL, STDNAME, STDCLASS

FROM   [dbo].[tbl_STUDENT];

GO

SELECT * FROM   [dbo].[view_STDDETALS];

Step-4 [ Use sp_HELP ]

SP_HELP      view_STDDETALS

Name              Owner Type  Created_datetime

view_STDDETALS    dbo         view  2013-12-27 16:39:54.880

Column_name Type  Computed    Length      Prec  Scale

STDROLL     int         no    4           10   0   

STDNAME     varchar     no    50              

STDCLASS    int         no    4           10   0  

Here we not find any base table information

Step-5 [ Use sp_describe_first_result_set ]

Here we are table some example columns only to understand the example

EXEC sp_describe_first_result_set

     N'SELECT * FROM view_STDDETALS', NULL, 0

name       system_type_name     max_length source_table

STDROLL    int                  4          NULL

STDNAME    varchar(50)          50         NULL

STDCLASS   int                  4          NULL

Please look here we are taking parameter as 0. So in source_table it not shows anything it just showing the columns name and data type of the view.

EXEC sp_describe_first_result_set

     N'SELECT * FROM view_STDDETALS', NULL, 1

name      system_type_name max_length     source_table

STDROLL    int             4              tbl_STUDENT    

STDNAME    varchar(50)      50             tbl_STUDENT    

STDCLASS   int              4              tbl_STUDENT

Please look here we are taking parameter as 1. So in source_table it shows the base table name of the view not the view name.

EXEC sp_describe_first_result_set

     N'SELECT * FROM view_STDDETALS', NULL, 2

name       system_type_name     max_length source_table

STDROLL    int                  4          view_STDDETALS

STDNAME    varchar(50)          50         view_STDDETALS

STDCLASS   int                  4          view_STDDETALS

Please look here we are taking parameter as 2. So in source_table it shows the view name.

Hope you like it.

Posted by: MR. JOYDEEP DAS

What to use ISNULL() or COALESCE()

Introduction

One of my Blog post is very much appritiated by my reader is different between ISNULL() and COALESCE (). But in early time I post my blogs at http://sqlservernet.blogspot.in/2012/03/what-to-use-isnull-or-coalesce.html

So I decide to move this post in my web that all my reader can enjoy it.

Let's start

The NULL value always makes some problem to database developer. To handle the null value properly the SQL server provides two functions:

1.    ISNULL()

2.    COALESCE ()

Both functions act like same.

Before finding the difference between two functions we must understand, what is the functionality of both functions to take care of null values.

Suppose we have a Table names TBL-A

Roll	StudentName	StudentClass
1	JOYDEEP	1
2	TUHIN	1
3	NULL	1

To handle the NULL value with ISNULL()

SELECT ISNULL(StudentName,'NOT FOUND') StudentName

FROM   Table1-A

It returns "NOT FOUND" in studentname columns for roll = 3

The syntax of the COALESCE is mentioned bellow

COALESCE ( expression  [ ,...n ] )

Unlike ISNULL, COALESCE takes multiple columns as expression. To understand it properly I give an example. Please study the example well and you will understand the COALESCE

CREATE TABLE dbo.wages

(

    emp_id             tinyint   identity,

    hourly_wage   decimal   NULL,

    salary               decimal   NULL,

    commission     decimal   NULL,

    num_sales       tinyint      NULL

);

GO

INSERT dbo.wages

    (hourly_wage, salary, commission, num_sales)

VALUES

    (10.00, NULL, NULL, NULL),

    (20.00, NULL, NULL, NULL),

    (30.00, NULL, NULL, NULL),

    (40.00, NULL, NULL, NULL),

    (NULL, 10000.00, NULL, NULL),

    (NULL, 20000.00, NULL, NULL),

    (NULL, 30000.00, NULL, NULL),

    (NULL, 40000.00, NULL, NULL),

    (NULL, NULL, 15000, 3),

    (NULL, NULL, 25000, 2),

    (NULL, NULL, 20000, 6),

    (NULL, NULL, 14000, 4);

GO

SELECT 

CAST(COALESCE(hourly_wage * 40 * 52,  salary,

                                commission * num_sales) AS money) AS 'Total Salary'

FROM dbo.wages

ORDER BY 'Total Salary';

The Output is mentioned bellow

 Total Salary

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

Now what are the differences between them?

1.    COALESCE is ANSI-92 compliant and ISNULL is not. So if u migrate the SQL statement in any          other RDBMS the COALESCE is helpful.

2.    COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order          to compare three expressions with ISNULL, you would have to nest expressions:

       SELECT ISNULL(ISNULL(Col1, Col2), Col3)

3.    In this SQL the ISNULL can provide some Error

       DECLARE @Field1 char(3),

               @Field2 char(50)

      SET @Field2 = 'Some Long String'

      SELECT ISNULL(@Field1, @Field2)

      --Returns 'Som'

      SELECT COALESCE(@Field1, @Field2)

      --Returns 'Some Long String'

 4.    Performance wise ISNULL and COALESCE are equal. It is found that the ISNULL is  

        slightly better performer then COALESCE.

In my advice use COALESCES() rather than ISNULL().

Hope you like this.

Posted by: MR. JOYDEEP DAS

EXECUTE in MS SQL 2012

Introduction

In MS SQL server 2008 and earlier version we are all familiar with Execute statement (EXEC). The EXEC is used to execute a dynamic query or a Stored Procedure (SP). But in MS SQL 2012 it has some improvement. In this article we try to explore it.

Why we Use EXEC

Here I am using EXEC to execute a stored procedure.

Here is my base table

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

   BEGIN

     DROP TABLE dbo.tbl_STUDENT;

   END

GO

CREATE TABLE dbo.tbl_STUDENT

    (STDROLLNO  INT            NOT NULL IDENTITY PRIMARY KEY,

     STDNAME    VARCHAR(50) NOT NULL);

GO

INSERT INTO dbo.tbl_STUDENT

       (STDNAME)

VALUES ('Joydeep Das'),('Archita Dutta'), ('Ranajit Dhar');

GO

Here the Table

SELECT * FROM dbo.tbl_STUDENT;

STDROLLNO  STDNAME

1          Joydeep Das

2          Archita Dutta

3          Ranajit Dhar

Now we create the stored procedure

IF OBJECT_ID(N'dbo.sproc_GETSTUDENT', N'P') IS NOT NULL

   BEGIN

      DROP PROCEDURE dbo.sproc_GETSTUDENT;

   END

GO

--Creating SP

CREATE PROCEDURE dbo.sproc_GETSTUDENT

AS

BEGIN

    SELECT STDROLLNO, STDNAME FROM  dbo.tbl_STUDENT;

END

                          

GO

Here we Execute the Stored procedure

EXEC  dbo.sproc_GETSTUDENT  

STDROLLNO  STDNAME

1          Joydeep Das

2          Archita Dutta

3          Ranajit Dhar

Look the output of the EXEC statement when we execute the stored procedure. The columns names are same that we used in the stored procedure.

If we want to change the column name we must alter the stored procedure by providing the alias name in the columns.

So what's New in MS SQL 2012

In MS SQL 2012 we can change the execution columns name which is executed by EXECUTE statement.

MS SQL Server 2012, improved version of EXECUTE keyword allows to modify the result set with changing the column name and data type without modifying the stored procedure.

Here is the Example

EXEC sproc_GETSTUDENT 

 WITH RESULT SETS 

 ( 

      ( 

             STUDENTROLLNO  INT,

             NAMEOFSTUDENT  VARCHAR(50)

      ) 

 );

STUDENTROLLNO   NAMEOFSTUDENT

1               Joydeep Das

2               Archita Dutta

3               Ranajit Dhar

Hope you like it.

Posted by: MR. JOYDEEP DAS

PARSE () in SQL 2012

Introduction

PARSE () function is Introduced at MS SQL Server 2012. Here in this article we are trying to illustrate the PARSE () function.

Why the PARSE ()

The PARSE () function is used to convert staring value to Numeric or Date/Time format. Please remember that PARSE () function can convert only string value. If the string value cannot converted to Numeric or Date/Time format it will provide an Error.

What it needs

PARSE () function needs common language run time (CLR) to convert sting value to Numeric or Date/Time format. If the CLR is not installed the PARSE () returns an Error.

To install CLR use

http://www.microsoft.com/en-in/download/details.aspx?id=35580

Syntax of PARSE ()

PARSE ( string_value As data_type  [Using culture])

String_value - The expression which needs to be parsed.

Data_type - To which data type we are converting to.

CULTUre - To which culture, i.e., language such as gb-en, us-en. This is an optional parameter.

Culture part of the function is optional. Language like English, Japanese, Spanish, Danish, French etc. to be used by MS SQL Server to interpret data. If the culture is not specified the culture of the current session is used. The culture can be any of the .NET supported cultures.

Example of PARSE ()

SELECT PARSE('08-04-2013' AS DATETIME USING 'en-US') AS Date

Date

2013-08-04 00:00:000

SELECT PARSE('200.000' AS INT) AS ValueInt

ValueInt

200

SELECT PARSE('July 30, 2013' AS DATETIME)
AS ValueDT

ValueDT

2013-07-30 00:00:00.000.

Hope you like it.

Posted by: MR. JOYDEEP DAS