Select columns from result set of stored procedure

Question

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
  

  Incorrect syntax near the keyword 'EXEC'.
  Incorrect syntax near ')'.

• I tried declaring a table variable and I got the following error
  

  Insert Error: Column name or number of supplied values does not match table definition

• If I try
  SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
  I get the error :
  

  Incorrect syntax near the keyword 'exec'.

Answer 1

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 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.")

Answer 2

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 http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx

Answer 3

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

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.

Answer 4

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Answer 5

(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.

Answer 6

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.