What is the purpose of "EXECUTE…WITH RESULT SETS" introduce with SQL Server 2012?

Answer:

The WITH RESULT SETS clause is related to the EXECUTE statement. This clause will
allow you to redefine the name and the data type for each column that’s in the result
set of the EXECUTE command.We can specify the new option with the EXECUTE statement when executing a stored procedure or a dynamic batch, like so:

EXECUTE WITH ;

E.g.

EXEC('SELECT 43112609 AS val;')

WITH RESULT SETS

(

  (

    val VARCHAR(10)

  )

 );

Henceforth, we can make out that, irrespective of the column name(s) returned in the result set, we can change the column names and it’s data Types as long as the data Type conversion is compatible with the original result set(i.e. the data types defined in the table schema). Else the database engine will report error.