Select columns from result set of stored procedure

I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like

SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'

When I used the above syntax I get the error:

> "Invalid Column Name".



I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.

Is there any way to do what I want?

- I could make a temp table to put the results in, but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns. I wanted to avoid tracking down all the columns that are returned.

- I tried using `WITH SprocResults AS ....` as suggested by Mark, but I got 2 errors <BR>
> Incorrect syntax near the keyword 'EXEC'.<BR>Incorrect syntax near ')'.

- I tried declaring a table variable and I got the following error <br>
>Insert Error: Column name or number of supplied values does not match table definition

- If I try <BR>
`SELECT * FROM EXEC MyStoredProc 'param1', 'param2'`<br>
I get the error :<br>
>Incorrect syntax near the keyword 'exec'.

Here's a link to a pretty good document explaining all the different ways to solve your problem (although a lot of them can't be used since you can't modify the existing stored procedure.)

[How to Share Data Between Stored Procedures][1]

Gulzar's answer will work (it is documented in the link above) but it's going to be a hassle to write (you'll need to specify all 80 column names in your @tablevar(col1,...) statement. And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out.




[1]:

[To see links please register here]

(Assuming SQL Server)

The only way to work with the results of a stored procedure in T-SQL is to use the `INSERT INTO ... EXEC` syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.

try this

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
'exec mydatabase.dbo.sp_onetwothree') AS a
GO

This works for me: (i.e. I only need 2 columns of the 30+ returned by `sp_help_job`)

SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER,
'EXEC msdb.dbo.sp_help_job @job_name = ''My Job'', @job_aspect = ''JOB''');


Before this would work, I needed to run this:

sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;

....to update the `sys.servers` table. (i.e. Using a self-reference within OPENQUERY seems to be disabled by default.)

For my simple requirement, I ran into none of the problems described in the [OPENQUERY section][1] of Lance's excellent link.

Rossini, if you need to dynamically set those input parameters, then use of OPENQUERY becomes a little more fiddly:

DECLARE @innerSql varchar(1000);
DECLARE @outerSql varchar(1000);

-- Set up the original stored proc definition.
SET @innerSql =
'EXEC msdb.dbo.sp_help_job @job_name = '''+@param1+''', @job_aspect = N'''+@param2+'''' ;

-- Handle quotes.
SET @innerSql = REPLACE(@innerSql, '''', '''''');

-- Set up the OPENQUERY definition.
SET @outerSql =
'SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER, ''' + @innerSql + ''');';

-- Execute.
EXEC (@outerSql);




I'm not sure of the differences (if any) between using `sp_serveroption` to update the existing `sys.servers` self-reference directly, vs. using `sp_addlinkedserver` (as described in Lance's link) to create a duplicate/alias.

Note 1:
I prefer OPENQUERY over OPENROWSET, given that OPENQUERY does not require the connection-string definition within the proc.

Note 2:
Having said all this: normally I would just use INSERT ... EXEC :) Yes, it's 10 mins extra typing, but if I can help it, I prefer not to jigger around with:
(a) quotes within quotes within quotes, and
(b) sys tables, and/or sneaky self-referencing Linked Server setups (i.e. for these, I need to plead my case to our all-powerful DBAs :)

However in this instance, I couldn't use a INSERT ... EXEC construct, as `sp_help_job` is already using one. ("An INSERT EXEC statement cannot be nested.")

[1]:

[To see links please register here]

CREATE TABLE #Result
(
ID int, Name varchar(500), Revenue money
)
INSERT #Result EXEC RevenueByAdvertiser '1/1/10', '2/1/10'
SELECT * FROM #Result ORDER BY Name
DROP TABLE #Result

Source:<br />

[To see links please register here]

A quick hack would be to add a new parameter `'@Column_Name'` and have the calling function define the column name to be retrieved. In the return part of your sproc, you would have if/else statements and return only the specified column, or if empty - return all.


CREATE PROCEDURE [dbo].[MySproc]
@Column_Name AS VARCHAR(50)
AS
BEGIN
IF (@Column_Name = 'ColumnName1')
BEGIN
SELECT @ColumnItem1 as 'ColumnName1'
END
ELSE
BEGIN
SELECT @ColumnItem1 as 'ColumnName1', @ColumnItem2 as 'ColumnName2', @ColumnItem3 as 'ColumnName3'
END
END

If you're doing this for manual validation of the data, you can do this with LINQPad.

Create a connection to the database in LinqPad then create C# statements similar to the following:

DataTable table = MyStoredProc (param1, param2).Tables[0];
(from row in table.AsEnumerable()
select new
{
Col1 = row.Field<string>("col1"),
Col2 = row.Field<string>("col2"),
}).Dump();

Reference

[To see links please register here]

Easiest way to do if you only need to this once:

Export to excel in Import and Export wizard and then import this excel into a table.

I'd cut and paste the original SP and delete all columns except the 2 you want. Or. I'd bring the result set back, map it to a proper business object, then LINQ out the two columns.