How to use ‘WITH RESULT SETS’ in SQL Server 2012


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
withresultset1Now 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)
)
)

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

Advertisements

Tagged: , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: