Solved

INSERT INTO EXEC WITH RESULT SETS

Posted on 2013-01-30
4
531 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 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