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
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 ));
- You cannot change the data type to any incompatible data type.
For example: Cannot change from varchar to int.
- You cannot reduce or increase the number of columns in result set.
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