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 :
- 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.
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
[…] SQL SERVER 2012– Executing Stored Procedure with Result Sets […]
[…] my previous article, I wrote about Executing Stored Procedure with Result Sets. I utilized this new feature introduced in SQL Server 2012 many times and it is very handy, when it […]
[…] EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this is one of the new error messages introduced in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS. […]
[…] EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), but the statement only sent %d result set(s) at run time is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS. […]
[…] In my earlier article, I discussed that if you need to use result set with any stored procedure than you cannot insert the result set into a table. It will generate the error no 102, Incorrect syntax near ‘SETS’. Once I posted that article, I was keep looking for the solution, because it is a very handy feature of SQL Server 2012. The detail of this feature is available here. […]
[…] EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS. […]
Hi Muhamad.
this feature is nice, but if the new function this only to rename the column name then following I think to do it just in Select statement is better than…
Thank you.
[…] EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid data type usage in the new feature EXEC WITH RESULT SETS. […]
[…] noticed in SQL Server 2012 and above. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS shipped with SQL Server […]