SQL Server Portal

In my earlier article, I discussed that if you need to use result set with any stored procedure then you cannot insert the result set into a table. It generates the error no 102,  Incorrect syntax near ‘SETS’. After I posted this article, I kept looking for the solution, because it is a very handy feature of SQL Server 2012. At last, I found the solution. The details of this feature are available here.

Let me discuss the Solution step by step.

Step 1 :
In Step 1, we need to create a table and stored procedure to explain the problem and solution.

USE AdventureWorks2012
GO
CREATE TABLE #temp
(
[DepartmentID] int,
[Name] varchar(50)
)
GO

CREATE PROCEDURE SP_ResultSet_Example1
AS
SELECT [DepartmentID]
,[Name]
FROM [HumanResources].[Department]
ORDER BY DepartmentID
GO

Step 2 :
In Step 2, I will show you the error if you try to execute the stored procedure (SP_ResultSet_Example1) using result set and insert the result set into the table (#temp).
Given below is the script.

USE AdventureWorks2012
GO
INSERT INTO #temp
EXEC SP_ResultSet_Example1
WITH RESULT SETS
(
([Department ID] INT NOT NULL,
[Department name] NAME NOT NULL));
GO
--OUTPUT

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘SETS’.

Ooopps…… I am unable to execute it.

Step 3 :
In Step 3, I will try to execute the stored procedure (SP_ResultSet_Example1) using result set and insert the result set into the table (#temp) successfully.
Given below is the script.

USE AdventureWorks2012
GO
DECLARE @Sql AS NVARCHAR(200)
SET @Sql='
EXEC SP_ResultSet_Example1
WITH RESULT SETS
(
([Department ID] INT NOT NULL,
[Department name] NAME NOT NULL));'

INSERT INTO #temp EXEC (@Sql)
GO
SELECT * FROM #temp
GO
--OUTPUT