Change Field Name and Datatype Using EXEC. Change the Field Name Without Altering Stored Procedure In SQL Server Denali (MSSQL2011)

SQL Server Denali has added a feature to existing EXECUTE you can define META DATA for result set of stored procedure. Many time you come across requirement where you have to set alias name of field or change datatype for a column in result set. To achieve this we generally alter the Stored Procedure. Now with extending functionality “WITH RESULT SET” of EXECUTE you can set alias for field and change their data type.

Let’s Explore It

Create Table and Stored Procedure With Following Script

CREATE TABLE [dbo].[Records](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [EmpName] [varchar](20) NULL,
  [AccountNo] [varchar](12) NULL,
  [IsActive] [tinyint] NULL,
  [Salary] [decimal](22, 2) NULL,
  [Department] [varchar](50) NULL
)
GO
INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department])
VALUES
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Create Procedure Usp_GetEmployee
As
Select Id,EmpName,AccountNo,IsActive,Salary,Department
From Records 

Go

Now Execute The Stored Procedure

Exec Usp_GetEmployee

Output:

You can see the above result returned from the stored procedure and suppose this stored procedure is being used by application at various places and you come across requirement to show “Id as EmployeeNumber” and “IsAcive as Status” and “Salary As CTC” .

So with the feature of WITH RESULT SET you can achieve this. See Following

Exec Usp_GetEmployee
WITH RESULT SETS
(
  (
      EmployeeNumber Int,
      EmpName Varchar(50),
      AccountNo Varchar(20),
      Status Tinyint,
      CTC Decimal(20,2),
      Department Varchar(50)
  )
)

Output:

If Stored Procedure Returns Multiple Result Set Then you can define SETS with comma Separated.

For Example:

Exec StoreProceName
WITH RESULT SETS
(
  (
          --Result Set 1
  ),
  (
          --Result Set 2
  )
)