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

It might be helpful to know why this is so difficult. A stored procedure may only return text (print 'text'), or may return multiple tables, or may return no tables at all.

So something like SELECT * FROM (exec sp_tables) Table1 will not work

Answer 2

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.

Answer 3

For SQL Server, I find that this works fine:

Create a temp table (or permanent table, doesn't really matter), and do a insert into statement against the stored procedure. The result set of the SP should match the columns in your table, otherwise you'll get an error.

Here's an example:

DECLARE @temp TABLE (firstname NVARCHAR(30), lastname nvarchar(50));

INSERT INTO @temp EXEC dbo.GetPersonName @param1,@param2;
-- assumption is that dbo.GetPersonName returns a table with firstname / lastname columns

SELECT * FROM @temp;

That's it!

Answer 4

If you are able to modify your stored procedure, you can easily put the required columns definitions as a parameter and use an auto-created temporary table:

CREATE PROCEDURE sp_GetDiffDataExample
      @columnsStatement NVARCHAR(MAX) -- required columns statement (e.g. "field1, field2")
AS
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
    EXEC sp_executeSql @query
    SELECT * FROM ##TempTable
    DROP TABLE ##TempTable
END

In this case you don't need to create a temp table manually - it is created automatically. Hope this helps.

Answer 5

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

Answer 6

To achieve this, first you create a #test_table like below:

create table #test_table(
    col1 int,
    col2 int,
   .
   .
   .
    col80 int
)

Now execute procedure and put value in #test_table:

insert into #test_table
EXEC MyStoredProc 'param1', 'param2'

Now you fetch the value from #test_table:

select col1,col2....,col80 from #test_table