Select columns from result set of stored procedure

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

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.

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

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!

As it's been mentioned in the question, it's hard to define the 80 column temp table before executing the stored procedure.

So the other way around this is to populate the table based on the stored procedure result set.

SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'
,'EXEC MyStoredProc')

If you are getting any error, you need to enable ad hoc distributed queries by executing following query.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

To execute `sp_configure` with both parameters to change a configuration option or to run the `RECONFIGURE` statement, you must be granted the `ALTER SETTINGS` server-level permission

Now you can select your specific columns from the generated table

SELECT col1, col2
FROM #temp

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

For anyone who has SQL 2012 or later, I was able to accomplish this with stored procedures that aren't dynamic and have the same columns output each time.

The general idea is I build the dynamic query to create, insert into, select from, and drop the temp table, and execute this after it's all generated. I dynamically generate the temp table by first [retrieving column names and types from the stored procedure](

[To see links please register here]

).

Note: there are much better, more universal solutions that will work with fewer lines of code if you're willing/able to update the SP or change configuration and use `OPENROWSET`. Use the below if you have no other way.



DECLARE @spName VARCHAR(MAX) = 'MyStoredProc'
DECLARE @tempTableName VARCHAR(MAX) = '#tempTable'

-- might need to update this if your param value is a string and you need to escape quotes
DECLARE @insertCommand VARCHAR(MAX) = 'INSERT INTO ' + @tempTableName + ' EXEC MyStoredProc @param=value'

DECLARE @createTableCommand VARCHAR(MAX)

-- update this to select the columns you want
DECLARE @selectCommand VARCHAR(MAX) = 'SELECT col1, col2 FROM ' + @tempTableName

DECLARE @dropCommand VARCHAR(MAX) = 'DROP TABLE ' + @tempTableName

-- Generate command to create temp table
SELECT @createTableCommand = 'CREATE TABLE ' + @tempTableName + ' (' +
STUFF
(
(
SELECT ', ' + CONCAT('[', name, ']', ' ', system_type_name)
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID(@spName),
NULL
)
FOR XML PATH('')
)
,1
,1
,''
) + ')'

EXEC( @createTableCommand + ' '+ @insertCommand + ' ' + @selectCommand + ' ' + @dropCommand)

I know executing from sp and insert into temp table or table variable would be an option but I don't think that's your requirement. As per your requirement this below query statement should work:

Declare @sql nvarchar(max)
Set @sql='SELECT col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);uid=test;pwd=test'',
''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
Exec(@sql)

if you have trusted connection then use this below query statement :

Declare @sql nvarchar(max)
Set @sql='SELECT col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',
''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
Exec(@sql)

if you are getting error to run the above statement then just run this statement below:

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

I hope this will help someone who will have faced this kind of similar problem. If someone would to try with temp table or table variable that should be like this below but in this scenario you should to know how many columns your sp is returning then you should create that much columns in temp table or table variable:

--for table variable
Declare @t table(col1 col1Type, col2 col2Type)
insert into @t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM @t

--for temp table
create table #t(col1 col1Type, col2 col2Type)
insert into #t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM #t

**Create a dynamic view and get result from it.......**

CREATE PROCEDURE dbo.usp_userwise_columns_value
(
@userid BIGINT
)
AS
BEGIN
DECLARE @maincmd NVARCHAR(max);
DECLARE @columnlist NVARCHAR(max);
DECLARE @columnname VARCHAR(150);
DECLARE @nickname VARCHAR(50);

SET @maincmd = '';
SET @columnname = '';
SET @columnlist = '';
SET @nickname = '';

DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
FOR
SELECT columnname , nickname
FROM dbo.v_userwise_columns
WHERE userid = @userid

OPEN CUR_COLUMNLIST
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END

FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @columnlist = @columnlist + @columnname + ','

FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname
END
CLOSE CUR_COLUMNLIST
DEALLOCATE CUR_COLUMNLIST

IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
BEGIN
SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END
ELSE
BEGIN
SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END

--PRINT @maincmd
EXECUTE sp_executesql @maincmd
END

-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value

Here's a simple answer:

SELECT ColA, ColB
FROM OPENROWSET('SQLNCLI','server=localhost;trusted_connection=yes;','exec schema.procedurename')

SQLNCLI is the native SQL client and "localhost" will cause it to utilize the server on which you are executing the procedure.

There's no need to build a temp table or any of that other jazz.