SQL Server Portal

In Sql server 2012,  Microsoft has introduced a very nice and interesting feature and that is execute stored procedure with result set.  With the help of feature, now we can change the column name & data type of the stored procedure result set.

Before SQL server 2012, we were doing the same but it was a lengthy procedure. First we had to create a temporary table and then execute the stored procedure and insert the result set in the temporary table and then select it.

Today, we will discuss the different aspects of this new feature.

Let me explain it with simple examples:

Example 1: (Stored procedure with Single Result set)

Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
,[ModifiedDate]
from  [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example1
GO

In the above example, we have three columns in the result set
[DeprtmentID],[Name],[ModifiedDate]

But, I would like to rename the column name and change the data type of Modified Date column in the result set like this
[Deprtment ID],[Department Name], [Modified Date]

Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example1
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
));
GO
--Result

Example 2: (Stored procedure with Multiple Result set)

The same way we can do it for multiple result sets also. Given below is the example.

--Without resultset
Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example2
as
Select [DepartmentID]
,[Name]
,ModifiedDate
from  [HumanResources].[Department]
Select 'Total' as [Total] ,Count(*) as [Count] from [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example2
GO

--With resultset
Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example2
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
),
(
[Total] varchar(5) NOT NULL,
[Department Count] Int NOT NULL
));

Limitations :

1. You cannot change the data type to any incompatible data type.

For example: Cannot change from varchar to int.

2. You cannot reduce or increase the number of columns in result set.

Summary:

In this article, we discussed how we can rename and change the data type of the stored procedure output. This feature is very handy especially for SQL Server Integration Services (SSIS) developers when they need to rename/ change the column name and data type respectively in the result  set.

Reference : MSDN

Read Full Post »