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