How to redefine the returning result sets of a stored procedure?

This feature was introduced in SQL Server 2012. Using With Result Sets option with Execute Command we can change the column name and data type of returning result set.

Ex:

Use TempDB 
Go 
Create Table Table1(ID int, Name varchar(10), DOB datetime)
   Go 
Insert Into Table1 values (1,’A’,’1980-04-10 06:09:00.100′),(1,’B’,’1982-03-08 03:42:41.002′)
   Go 
Create Procedure Procedure1 
As 
Begin 
Select ID, Name, DOB 
From Table1 
End 
Go
Exec Procedure1 
Go 
Exec Procedure1 With Result Sets ((MyID bigint Not Null, MyName varchar(7) Not Null, DateOfBirth Date Not Null)); 
Go 
Drop Table Table1 
Drop Procedure Procedure1

Output: 
(2 row(s) affected) 
ID          Name       DOB 
———– ———- ———————– 
1           A          1980-04-10 06:09:00.100 
1           B          1982-03-08 03:42:41.003

(2 row(s) affected)

MyID                 MyName  DateOfBirth 
——————– ——- ———– 
1                    A       1980-04-10 
1                    B       1982-03-08

(2 row(s) affected)

The second result set output is based on the defination given in With Result Sets.