By: Rick Dobson | Updated: 2019-09-02 | Comments | Related: > Stored Procedures
Problem
I am encountering requirements where I need to process a results set from a SQL Server stored procedure in another application or I am asked to pass the results set generated by a stored procedure to another team for data mining. What are some ways of making a results set from a stored procedure available for data mining or data science projects? Please also demonstrate how to store for subsequent use more than one results set from a single stored procedure.
Solution
When you run a stored procedure with one or more select statements, SQL Server displays a results set for each SELECT statement. While it is sometimes enough to just view the results set from a stored procedure, it can easily happen that you or a data mining colleague need the results set for subsequent processing. This tip equips you to persist one or more results sets from a stored procedure. If you need a refresher on creating, altering, dropping, and executing stored procedures, you can review a prior tip, Create, Alter, Drop and Execute SQL Server Stored Procedures, in this series on stored procedures and two other kinds of code modules (user-defined functions and views).
The general solution for persisting a results set from a stored procedure is to store the results set(s) in one or more tables. You can use any of several different types of tables, including regular tables, local temp tables and global temp tables. This tip includes three examples that reveal how to persist a results set from a stored procedure with each of these options. This tip also demonstrates how to persist more than one results set from a single stored procedure.
Copying a SQL Server Stored Procedure’s Results Set to a Local Temp Table
You can copy the results set from a stored procedure to a local temp table in a three-step process.
- In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist.
- In the second step, create a local temp table outside of the stored procedure.
- With a local temp table, the data in the table exists for the duration of the session creating the local temp table and goes out of scope automatically when the session creating the local temp table closes.
- In contrast, the data in a regular table can exist until the table is dropped.
- In the third step, run the stored procedure so that its results set becomes the source for populating the table.
Here’s an example of a simple script for the first step.
- The use statement sets the database context. This is the database where the stored procedure object will be saved. The data source for the select statement inside a stored procedure can be from a different database.
- The next code block in the script drops a stored procedure named uspMySecondStoredProcedure in the dbo schema if it exists already; the dbo schema will be used in all samples throughout this tip. It is necessary to include a go statement after the conditional drop proc formulation so that create proc in the next statement is at the top of its batch.
- The create proc statement has two main roles for the purposes of this tip.
- First, it adds a new stored procedure to the database in which it runs.
- Second, it contains a select statement that displays a results set.
-- set a default database context
use CodeModuleTypes
go
-- conditionally drop a stored proc
if object_id('dbo.uspMySecondStoredProcedure') is not null
drop proc dbo.uspMySecondStoredProcedure
go
-- create a new stored proc to display a results set
create proc dbo.uspMySecondStoredProcedure
as
-- select statement for the results set
-- that the stored proc displays
select
SalesOrderID
,OrderDate
,CustomerID
,SalesPersonID
,TotalDue
from AdventureWorks2014.Sales.SalesOrderHeader
After you create the stored procedure, you need to run it and store the generated results set in a table. The next script demonstrates one approach to accomplishing these goals.
- The target table for storing the results set is a local temp table named #soh. If you want a refresher on temp tables, see this prior tip.
- After creating the temp table, the script runs uspMySecondStoredProcedure as the source for an insert into statement for the #soh table.
- In this example, the identity_insert property is manipulated to facilitate
the insert statement. The manipulation allows the code to populate a column
with an identity property with values from a source (see this
tip
for a refresher on manipulating the identity_insert property).
- The identity_insert property is turned on just before invoking the insert into statement.
- The identity_insert property is restored to its default value of off right after the exec statement for the stored procedure.
- The final select statement in the script displays the stored procedure’s results set from the local temp table. You can use table values in any ways that your needs dictate, such as to compute sales taxes for orders.
-- prepare a local temp table to capture
-- the results set displayed by the stored proc
if object_id('tempdb..#soh') is not null
drop table #soh
create table #soh(
SalesOrderID int identity(1,1) not null
,OrderDate datetime not null
,CustomerID int not null
,SalesPersonID int null
,TotalDue money not null
)
-- manipulate IDENTITY_INSERT property for
-- #soh table while inserting rows
-- from the stored procedure into the table
set identity_insert #soh on
insert into #soh
(
SalesOrderID
,OrderDate
,CustomerID
,SalesPersonID
,TotalDue
)
exec uspMySecondStoredProcedure
set identity_insert #soh off
-- display values deposited into #soh
select * from #soh
Saving the Results Set from a SELECT Statement within a SQL Server Stored Procedure to a Regular Table
By saving a select statement’s results set to a regular table from inside a stored procedure, the process for persisting a results set can be even simpler than with the preceding example. There are two main reasons that the process is simpler.
- Regular tables, in contrast to local temp tables, can have the same scope whether they are populated from inside or outside of a stored procedure. Their scope extends beyond the stored procedure. The scope of a local temp table created and populated from within a stored procedure is limited to the stored procedure.
- You can transfer the results set from a select statement to a regular table from an into clause within a select statement. Insert the into clause after the select list and before the from clause. Name the regular table in the into clause. The table must reside in the current database context. When the limitations of an into clause are acceptable, then the into clause offers a very attractive solution for persisting data from a stored procedure.
The following script excerpt shows an approach to populating a regular table from within a stored procedure.
- The database context in the example has the name CodeModuleTypes.
- The stored procedure with the select statement has the name uspMySecondStoredProcedure
in the dbo schema. A conditional drop proc formulation for uspMySecondStoredProcedure
followed by a conditional drop table formulation for soh_col_subset in the dbo
schema concludes with the go keyword.
- The conditional drop formulations remove prior versions of the stored procedure that creates a results set and the table persisting the results set.
- The go keyword allows the next create proc statement to appear at the top of its batch.
- The into clause within the select statement in uspMySecondStoredProcedure copies the select statement’s results set to a regular table named soh_col_subset in the dbo schema of the CodeModuleTypes; one limitation of the into keyword is that it can only transfer data to objects within the current database context.
- The code to create the stored proc only needs to run once. The go keyword after the create proc statement completes the creation of the stored procedure before an attempt is made to execute the stored procedure within the same code block.
- The line of dashes after the go keyword at the end of the create proc statement separates the create proc statement from a trailing exec statement.
- The code after the line of dashes includes an exec statement for the stored procedure followed by a select statement to display the results set from the soh_col_subset regular table.
-- specify a default database context
-- such as one in which a data miner has
-- permission to mine data
use CodeModuleTypes
go
-- conditionally drop a stored proc
if object_id('dbo.uspMySecondStoredProcedure') is not null
drop proc dbo.uspMySecondStoredProcedure
-- conditionally drop a regular table
if object_id('dbo.soh_col_subset') is not null
drop table dbo.soh_col_subset
go
-- create a new stored proc that populates
-- with an into clause within a select statement
-- a regular table with a results set
-- for processing outside the stored proc
create proc dbo.uspMySecondStoredProcedure
as
select
SalesOrderID
,OrderDate
,CustomerID
,SalesPersonID
,TotalDue
into soh_col_subset
from AdventureWorks2014.Sales.SalesOrderHeader
go
------------------------------------------------------
-- run uspMySecondStoredProcedure
exec uspMySecondStoredProcedure
-- display stored proc results set
-- from regular table populated inside
-- the stored proc
select * from dbo.soh_col_subset
The following screen shot shows from Object Explorer the soh_col_subset table design created by the into clause in the preceding script. Notice the into clause does not include the identity property from the SalesOrderID column in the SalesOrderHeader table from the AdventureWorks2014 database. The insert into and select into statements handle source data with an identity column somewhat differently. Recall it was necessary to manipulate the identity_insert property when copying with an insert into statement to a local temp.
The empty results set from the following script confirms that all the row values for the selected columns from the SalesOrderHeader table match the row values within the soh_col_subset table. The except operator returns all rows from the results set before it that are missing from the results set after it. If your requirements are for the identity values and not the identity property, you can simplify your code by using a select into statement versus an insert into statement.
-- the overlap of values is perfect -- because all the column values in the -- SalesOrderHeader table are also -- in the soh_col_subset table select SalesOrderID ,OrderDate ,CustomerID ,SalesPersonID ,TotalDue from AdventureWorks2014.Sales.SalesOrderHeader except select * from dbo.soh_col_subset
Storing Results Sets from Two SELECT Statements for Use Outside a SQL Server Stored Procedure
Up until this point, this tip focused on extracting just one results set to a table from one select statement inside a stored procedure. However, one special capability of a stored procedure is that it can return results sets from multiple select statements. This section shows you how to transfer multiple results sets from a stored procedure for use outside of a stored procedure.
You could simply specify an into clause for each select statement to make the results sets for each select statement available in a regular table after the stored procedure runs. However, this approach requires you to manage the lifecycle of the regular tables. Another approach is to transfer from within the stored procedure the results set for each select statement to a global temp table. Because the scope of a global temp table extends to the scope of the session in which it was created, then the transferred results set remains available for as long as you keep the session running. When you close the session from which you invoked the stored procedure, the global temp tables created within the session automatically ceases to exist as soon as no users have connections to a global temp table. This is a great feature so long as you only need a data source for the duration of a session.
Here’s a script to demonstrate an approach for transferring multiple results sets from a stored procedure via global temp tables.
- The script starts with a specification for a fresh version of uspMySecondStoredProcedure.
- First, any prior version that exists is dropped.
- Next, a create proc statement names the stored procedure and is followed by the as keyword with the defining T-SQL code for the stored procedure.
- A trailing go keyword follows the code to perform the operations encapsulated in the stored proc.
- The encapsulated code has these elements.
- First, it creates fresh versions of ##soh and ##customer global temp tables.
- Next, it uses insert into statements to populate both global temp tables from select statements from regular tables in the AdvertureWorks2014 database. These insert into statements are embedded inside code that turns the identity_insert property on and off for the population of each global temp table. Recall that this kind of manipulation preserves the identity property in a table holding values from a select statement.
-- conditionally drop a stored proc
if object_id('dbo.uspMySecondStoredProcedure') is not null
drop proc dbo.uspMySecondStoredProcedure
go
-- create a new stored proc
create proc dbo.uspMySecondStoredProcedure
as
-- conditionally drop and create
-- the ##soh and ##customer global temp tables
if object_id('tempdb..##soh') is not null
drop table ##soh
create table ##soh(
SalesOrderID int identity(1,1) not null
,OrderDate datetime not null
,CustomerID int not null
,SalesPersonID int null
,TotalDue money not null
)
if object_id('tempdb..##customer') is not null
drop table ##customer
create table ##customer(
CustomerID int identity(1,1) not null
,PersonID int null
,StoreID int null
)
-- populate the ##soh global temporary table
set identity_insert ##soh on;
insert into ##soh (
SalesOrderID
,OrderDate
,CustomerID
,SalesPersonID
,TotalDue
)
select
SalesOrderID
,OrderDate
,CustomerID
,SalesPersonID
,TotalDue
from AdventureWorks2014.Sales.SalesOrderHeader
set identity_insert ##soh off;
-- populate the ##customer global temporary table
set identity_insert ##customer on;
insert into ##customer (
CustomerID
,PersonID
,StoreID
)
select
CustomerID
,PersonID
,StoreID
from AdventureWorks2014.Sales.Customer
set identity_insert ##customer off;
go
After creating a fresh version of uspMySecondStoredProcedure, you can run an exec statement in another script that runs the stored proc and then use the global temp tables in any way your requirements dictate. The following example merely lists the contents of both tables.
-- run uspMySecondStoredProcedure exec uspMySecondStoredProcedure -- display two materialized results sets -- display ##soh select * from ##soh -- display ##customer select * from ##customer
Next Steps
- You can try out the code examples for this tip on a SQL Server with the AdventureWorks2014 database. This prior MSSQLTips.com tip describes how to download a copy of the AdventureWorks2014 database.
- Next, copy the script(s) that you want to test and modify. Confirm your copied code generates valid results for the AdventureWorks2014 database.
- Finally, modify the script(s) to reference another source database with selected columns from tables that follow from your requirements.
About the author
Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-09-02
