asked on

INSERT INTO EXEC WITH RESULT SETS

I wish to use the Result Sets with EXEC and Wanted ton insert Result Set in to Temp table.

CREATE TABLE #TEMP
(
Col1 nvarchar(50)
)
INSERT INTO #TEMP 
EXEC [dbo].[usp_GetProjectByEmployeeNo] @EmployeeNo = N'9999'
WITH RESULT SETS
((
Col1 nvarchar(50)
));
GO

Open in new window

I am getting error "Incorrect syntax near SETS"

deviprasadg

The RESULT SETS options cannot be specified in an INSERT…EXEC statement

Refer: http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx

Also you can accomplish your task by removing the with result sets section from the above query.

CREATE TABLE #TEMP
(
Col1 nvarchar(50)
)
INSERT INTO #TEMP 
EXEC [dbo].[usp_GetProjectByEmployeeNo] @EmployeeNo = N'9999'

Open in new window

Mark Wills

Correct, you do not use the result sets.

Given the current use of RESULT SETS then the SP is currently returning a single column that matches the Temp Table.

Result Sets in that case are a good reminder of the output.

However, worth remembering, your temp table will need to accomodate the entire result, it wont automatically concatenate into one column.

So, if you stored procedure returned : ProjectID, ProjectName, DueDate
then your Temp Table needs to be able to store those three columns.

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

ASKER

I am closing the Question by selecting my answer because, I have the same knowledge as provided by "deviprasadg".