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

1 comment:

  1. its realy very new to us, but I have one more question.
    Can we insert output of Stored Procedure directly into the undefined table??

    ReplyDelete