• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

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"
0
Alpesh Patel
Asked:
Alpesh Patel
  • 2
1 Solution
 
deviprasadgCommented:
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

0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Alpesh PatelAssistant ConsultantAuthor Commented:
I am closing the Question by selecting my answer because, I have the same knowledge as provided by "deviprasadg".
0
 
Alpesh PatelAssistant ConsultantAuthor Commented:
I am closing the Question by selecting my answer because, I have the same knowledge as provided by "deviprasadg".
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now