SQL Server Portal

In 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 comes to manipulate stored procedure result sets.

Today, I was reading a question related to this topic in a forum where a community member tried to manipulate stored procedure result set with result sets keyword and then insert it into a temporary table. But it gives an error.

Let me create few examples to explain the problem :

Example 1 :
In this example, we will insert the result set of a stored procedure in a temporary table without using result set keyword and it works fine.

Use AdventureWorks2012
GO
Create Table #temp
(
[DepartmentID] int,
[Name] varchar(50)
)
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
from [HumanResources].[Department]
GO
Insert into #temp EXEC SP_ResultSet_Example1
GO
Select * from #temp order by [DepartmentID]

Example 2 :
In this example, we will insert the result set of a stored procedure in a temporary table with result sets keyword. It gives an error .

Use AdventureWorks2012
GO
Create Table #temp
(
[DepartmentID] int,
[Name] varchar(50)
)
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
from [HumanResources].[Department]
GO

Insert into #temp EXEC SP_ResultSet_Example1 WITH RESULT SETS
(
([Department ID] int NOT NULL,
[Department name] Name NOT NULL));
GO
Select * from #temp order by [DepartmentID]

Explanation :
We cannot use result sets keyword, when we need to insert any stored procedure result set into a table. The reason behind this is if we use result sets keyword we can return multiple results with different columns and with different data types from a single stored procedure and this functionality cannot be handled in a single table. As per MSDN ” WITH
Possible execute options. The RESULT SETS options cannot be specified in an INSERT…EXEC statement.” .

Read Full Post »