Database Simplified Headline Animator

Database Simplified

Sunday 2 October 2011

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
INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department])
('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')

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


Now Execute The Stored Procedure

Exec Usp_GetEmployee



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
EmployeeNumber Int,
EmpName Varchar(50),
AccountNo Varchar(20),
Status Tinyint,
CTC Decimal(20,2),
Department Varchar(50)



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

For Example:

Exec StoreProceName
--Result Set 1
--Result Set 2

No comments:

Post a Comment