In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
go
CREATE PROC Usp_products
AS
SELECT ProductID,Name,ProductNumber FROM Production.Product
ORDER BY ProductID

Let us now execute the above procedure and see the outcome
Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
WITH RESULT SETS
(
(
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)
)
)

So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

Related articles

• CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com)
• Format() function in SQL Server 2012 (appliedsql.wordpress.com)

Tagged: Data definition language, Data type, Microsoft SQL Server, ResultSet, Select (SQL), SQL, SQL Server 2012, Stored procedure, With Result Set