SQL to select from a stored procedure rowset into a temporary table

Older stored procedures can return results simply as a set of rows from a select statement.

If you are calling the stored procedure from a SQL script, it can be handy to cache the results in a temporary table, so you can manipulate the rows.

1. use OPENROWSET to select from Stored Procedure -> Table
Here is a bit of SQL that uses OPENROWSET to select the rows from a stored procedure, into a temporary table.

 --you need to enable openrowset: 

 sp_configure 'show advanced options', 1; 

 RECONFIGURE 

 sp_configure 'ad hoc distributed queries', 1; 

 RECONFIGURE 

 --select the rowset from the stored procedure, into a temporary table: 

 select 

 * 

 into #licList 

 from 

 openrowset 

 ('SQLNCLI', 

 'server=LING-BUILD;Trusted_Connection=yes;', 

 'SET FMTONLY OFF; exec licensing_dev.dbo.spLicence_DetailsLicenceList ''PP'' ' 

 ) 

 --you can now manipulate the data output from the stored procedure: 

 select 

 * 

 from #licList 

 order by LicenceAuto  

2. use INSERT EXEC to select from Stored Procedure -> Table
here is another way, without need for OPENROWSET.
The problem with this method, is you need to declare a temp table, and this creates a maintenance overhead,
since if you add a column to the output of the  Stored Procedure, you will (probably) have to update the temp table too:

 create table #renewalsSelected



 ( 

 myAuto int, 

 createdDate datetime, 

 Descript nvarchar(max) 

 ) 

 INSERT INTO 

 #renewalsSelected 

 EXEC dbo.spMyStoredProcedureThatSelectsRows 

 --you can now manipulate the output of the stored procedure, by using the temp table #renewalsSelected 

 select 

 * 

 from 

 #renewalsSelected