What is Execute Procedure WITH Result SETS in SQL Server?


SQL Server 2012 provide feature to Execute Procedure WITH Result SETS. This feature help user to shape data in specific manner. We can redefine Column datatype and column Name in result set.
Syntax:
Execute Proc_Name
WITH Result SETS
((
Column_Name with less length
));
Example:

Create Procedure uspGetEmployee
As
Begin

Create table #Temp
(
  ID  int identity(1,1) ,
  EmployeeName varchar(100),
  Age int 
)
 
Insert into #Temp(EmployeeName,Age)
Select 'Hitesh',25 union
Select 'Priyanka',23 union
Select 'Dev',33 union
Select 'Neha',25 union
Select 'Abhimanyu',22 union
Select 'Bhawesh',30

Select ID,EmployeeName,Age from #Temp
Drop table #Temp
End


Execute Procedure WITH Result SETS.

Execute uspGetEmployee
WITH Result SETS
 ((  
    ID int,  
    EmployeeName VARCHAR(5),  
    Age int
 ));  


In this example we have reduce the length of EmployeeName to 5.
Procedure will return data with EmployeeName of length 5.

Execute Procedure WITH Result SETS have two options :
WITH Result SETS None Option;
WITH Result SETS Undefined Option;

WITH Result SETS None Option: It is used when procedure does not return any dataset.
If procedure return dataset and None Option is used then the following error will return.
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
Syntax:
Execute uspGetEmployee
WITH Result SETS None;
WITH Result SETS Undefined Option: It is default. When no dataset option is defined.
If procedure does not return any dataset and Undefined Option is used then it does not throw any error.
Syntax:
Execute uspGetEmployee
WITH Result SETS Undefined;