WITH RESULT SETS

SQL Server 2012 introduces new clause WITH RESULT SETS which allows us to redefine the Column Names and Data types of columns being returned from from Stored Procedure.

Limitations of WITH RESULT  SETS:

1. WITH RESULT SETS can only be used with Execute statement
2. WITH RESULT SETS cannot be used with INSERT Statement.
3.  Cannot change the number of columns returned by Result sets.

Example:

Creating a Stored Procedure:

Create proc Example_Withresultsets

as

begin

       Select top 10 concat(Firstname,'.',Middlename,'.',LastName) as PersonName,BusinessEntityID as EntityID from person.Person

      

end

/* Execute Stored Procedure*/

Exec Example_Withresultsets

/* Executing Stored Procedure with result sets clause*/

Exec Example_Withresultsets

with result sets

((

PersonFullName Varchar(100),

BusinessEntityID int

))

In the above image you can find that the column names are changed second resut set and this result is achieved by using the WITH RESULT SETS clause along with Execute statement.