By Liran Vaiman, Valinor SLA Manager & Data Expert

Many times I’ve wondered why so often people who work in SQL will use ‘Select Into’ and why we almost never use ‘Exec Into’. Of course, you can use OPENROWSET to execute a procedure directly into a table, but that requires allowing remote access to the server (which has security ramifications, and hence complications) as well as enabling queries that are distributed ad-hoc. To utilize the OPENROWSET command,you need to insert the connection string correctly and to specify the full path of the Stored Procedure (SP), including the database name and the SP’s owner.

And if that wasn’t enough of a hassle, in order to insert into a TEMP table with OPENROWSET FMTONLY needs to be set to OFF (this means that the SP will run twice).

At Valinor, I have come across many cases where our clients have multiple procedures that fill tables with data. That is why I created a SP that will return a table to read from, which is simpler and faster than the OPENROWSET solution: ‘spGetTableFromProcedure’. This SP can capture multiple result sets delivered by a single stored procedure execution, as long as all the result sets have the same Schema (column number, type and order).

This SP utilizes 5 parameters:

@Sp_Name: This param holds the name of the SP.

@DBName: The DB name you want to execute from. default is NULL which will give current DB.

@SchemaName: The SP’s schema. Default is ‘dbo’.

@Parameters: The parameters the SP gets. Default is NULL.

@NewTable: Will hold the name of the new table you want to EXEC INTO. Param default is ‘##TempFor’.

It should be noted that if you’ll ask for a regular temp table (#Temp) it will be changed to ‘##Temp’ in any case, because the #Temp table is created at a “lower level” than the block where the code is executed.

Of course, other SPs that use temp table can’t utilize the ‘EXEC INTO’ order, so be careful where you use the procedure.

Whether you are a programmer, a BI expert or a DBA, I hope this solution helps you fill tables with data faster and more efficiently.

Download the script: Get Table From Procedure .

‍

‍

‍