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.
WITH Result SETS
Column_Name with less length
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.
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.
WITH Result SETS Undefined;