Sometimes developers need to insert the result of a stored procedure into a temporary table rather than just display the results. The stored procedure may contain select statements, the result of which might be require to store in temporary tables for further processing on it. We can store the result set in local temp tables, global temp tables, and regular tables. It is always a good idea to display line numbers in SSMS to work better with a large stored procedure or other complex coding.
In this article, we will store the results of a stored procedure to temp table. We will accomplish this by using the following 3 small steps.
- Create a local temporary table
- Execute the INSERT statement and Stored procedure
- Select the data from the temporary table
As a first step, create a temporary table that will be used to hold the stored procedure’s results. It is important to create the temporary table with the same number of columns and with the same data types as of the returned values from the stored procedure. For example, if the stored procedure returns 3 different values with datatypes of Varchar, integer, and Varchar, then 3 columns of the same data types should be available in the temporary table to receive those values. In this example, we execute the sp_databases system stored procedure which returns the database name, database size, and remarks values for a particular SQL instance. So, we will create temporary tables according to the returned values as mentioned in the below script.
Create Table #Temp_Table (
DB_Name Varchar (100),
DB_Size Int,
Remarks Varchar (500));
As a second step, we execute the INSERT statement and System Stored procedure mentioned below in the script. It will insert the record in the table returned from the stored procedure.
– – Insert statement and execute system stored procedure
Insert Into #Temp_Table Execute sp_databases;
Once the temporary table is populated with the returned values from the stored procedure, execute a simple select statement on the temporary table to get the records. You can perform any other operation on the data stored in the temporary table. It is always a good practice to drop the temporary table once it is no more needed.
– – Return the records from the temporary table
Select * From #Temp_Table;
– – Drop Temporary table
Drop Table #Temp_Table;