WITH RESULT SETS: T-SQL Features in SQL Server 2012

WITH RESULT SETS: T-SQL Features in SQL Server 2012

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. 

In earlier versions of SQL server, to change a column name or data type in the resultset of a stored procedure, all the references needed to be changed. There was no simple way to dump the output of a stored procedure without worrying about the column names and data types.

·        With SQL Server 2012, the new WITH RESULT SETS feature avoids the requirement to change the stored procedure in order to change the column names in a resultset.

·        This feature is mainly used when executing a stored procedure in SSIS tasks. While executing any stored procedure in OLEDB Source, it is possible to execute the procedure with the required column names and data types.

Example:

CREATE PROCEDURE Employee_WithResultSet

AS

BEGIN

       SELECT 1 as EmpID,’John’  Name, ‘Clerk’ AS Dept UNION ALL

       SELECT 2 as EmpID,’Smith’ Name, ‘Manager’ AS Dept UNION ALL

       SELECT 3 as EmpID,’Tom’ Name, ‘Clerk’ AS Dept UNION ALL

       SELECT 4 as EmpID,’Merry’ Name, ‘Clerk’ AS Dept

END

GO

EXEC Employee_WithResultSet

WITH RESULT SETS

(

       (      EmpID int,

              EmpName varchar(50),

              EmpDept varchar(20)

       ) 

)

Result:

EmpID	EmpName	EmpDept
1	John	Clerk
2	Smith	Manager
3	Tom	Clerk
4	Merry	Clerk

Here, column name and data type can be changed independent of what is column name returned in the resultset.

In the above example ‘Name’ is changed to ‘EmpName’ and ‘Dept’ is changed to ‘EmpDept’. This can be helpful for using an appropriate data type while showing the resultset.