Thursday, March 06, 2014

WithResultSet in SQL Server 2012


WITH RESULT SETS
The EXECUTE statement has been extended in SQL Server 2012 to include the WITH
RESULT SETS option. This allows you to change the column names and data types
of the result set returned in the execution of a stored procedure.
For Example the following procedure returns a straightforward result set using the Employee table
CREATE PROC spGet_Employees
AS
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY EmployeeID
If we call this stored procedure in the usual way it will return all columns.
The data type of each column will be the same as the column type in the table.
EXEC spGet_Employees
We want to return the result set so the integer EmployeeID column is a varchar instead.
To see how you can easily change the name of the columns, we will output EmployeeID as
NI_Number and LastName as Surname. We can do this easily using WITH RESULT SETS:
EXEC spGet_Employees
WITH RESULT SETS
(
(
NI_Number varchar(15),
FirstName varchar(30),
Surname varchar(30)
)
)
NI_NUMBER
FirstName
LASTNAME
1
John
M
2
Ram
S
3
RAJ
D
5
Mano
C

No comments:

Post a Comment