Solved

INSERT INTO EXEC WITH RESULT SETS

Posted on 2013-01-30
4
529 Views
Last Modified: 2013-03-17
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
Comment
Question by:Alpesh Patel
  • 2
4 Comments
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38838337
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38839067
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 0 total points
ID: 38977493
I am closing the Question by selecting my answer because, I have the same knowledge as provided by "deviprasadg".
0
 
LVL 21

Author Closing Comment

by:Alpesh Patel
ID: 38993061
I am closing the Question by selecting my answer because, I have the same knowledge as provided by "deviprasadg".
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now