Friday, 27 December 2013

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

Tuesday, 24 December 2013

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

Thursday, 19 December 2013

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

Wednesday, 18 December 2013

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


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

Monday, 16 December 2013

Error Handling with THROW clause at SQL 2012


Introduction

Before MS SQL 2005 developer knows how hard to trap an error by using @@ERROR variable value. But from MS SQL 2005 we use TRY… CATCH blog to trap an error. It is so easy that all the SQL developer is just flying on the sky.

So what's new in MS SQL 2012
As a simple question is that what's new at MS SQL 2012 for Error handling as we are very happy with MS from MS SQL 2005 onward.

Yes MS is now trying us to send at Moon surface with SQL 2012.  Just a recall of TRY …CATCH block of Error handling.

 BEGIN
   DECLARE @v_NO       INT;
   DECLARE @v_ErrSen   INT,
           @v_ErrMsg   VARCHAR(max),
           @v_ErrState INT;
          
   BEGIN TRY
        SET @v_NO = 1000;
        SET @v_NO = @v_NO / 0;
   END TRY
   BEGIN CATCH
       SET @v_ErrSen = ERROR_SEVERITY();
       SET @v_ErrMsg = ERROR_MESSAGE();
       SET @v_ErrState = ERROR_STATE();
      
       RAISERROR(@v_ErrMsg, @v_ErrSen, @v_ErrState);
   END CATCH
END

Please look at the BEGIN CATCH… END CATCH section. Here we use RAISERROR() which takes three parameters.

IN MS SQL 2012 There is no need to understand RAISERROR() just Use THROW only

Code sample is given bellow

BEGIN
   BEGIN TRY
        SET @v_NO = 1000;
        SET @v_NO = @v_NO / 0;
   END TRY
   BEGIN CATCH
       THROW;
   END CATCH
END

Is it not easier then the complex RAISERROR(). A new developer can set it easily at SQL 2012.

Thanks to MS for that.



Hope you like it.




Posted by: MR. JOYDEEP DAS