Friday, November 25, 2011

SQL Server 2012 - Improved Version of EXECUTE keyword

In Previous version of SQL Server 2005/2008, if you execute the SELECT Stored Procedure using EXECUTE keyword, it will return all the records as it is. if you want to edit the column name and data type of result set, you have to modify the stored procedure respectively.

But in 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.

To explore this features, execute the following script to create a table, insert the record and creating the stored procedure.

 CREATE TABLE Customers  
(
ID INT IDENTITY(1,1),
FirstName VARCHAR(50),
LastName VARCHAR(50),
DOB DATE
)
GO
INSERT INTO Customers (FirstName, LastName, DOB)
SELECT 'Ayyanar', 'Jayabalan', '08/12/1982'
UNION ALL
SELECT 'Greg', 'Damico', '08/12/1970'
GO
CREATE PROCEDURE Customers_SelectAll
AS
BEGIN
SELECT
ID,
FirstName,
Lastname,
DOB
FROM dbo.Customers
END
GO


In SQL Server 2005/2008:
 -- SQL 2005/2008  
EXECUTE Customers_SelectAll


RESULT:


In SQL Server 2012:
Here i am changing the column "id" into "CustomerId" and also changing the data type of firstname from VARCHAR(50) to VARCHAR(4). Since i reduced the length of FirstName to 4, it shows truncated firstname value.
 -- SQL 2012  
EXEC Customers_SelectAll
WITH RESULT SETS
(
(
CustomerID SMALLINT,
FirstName VARCHAR(4),
Lastname VARCHAR(20),
DateOfBirth DATE
)
);


RESULT:

No comments: