Stored Procedures: Returning Data Using a Result Set

For this article, we are going to examine the benefit of using stored procedures that simply return a result set (i.e. a stored procedure that has a SELECT statement).

Before we do a shallow dive into this functionality, it is worth noting that SQL Server has three methods of returning data from a stored procedure: 1) result sets, 2) output parameters, and 3) return codes.

To get a quick overview of these methods, refer to the following documentation provided by Microsoft. We are going to focus on the 1st method, result sets.

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure

---

Why would we want to return a result set from a stored procedure when we could easily use a view or a table-valued function?

For one, these types of stored procedures work very well with Azure Data Factory, as described in this document.

Looking at the source options of an ADF “Copy data” activity, we can see the option to use a table, query, or stored procedure.

The stored procedure gives us the benefit of doing all sorts of data manipulation activities before performing the SELECT statement to be used by the copy activity. We can create a complicated extract using temporary tables, insert into log tables, or delete and update data. We can then send a result set over to ADF all within one single database object, saving us the need to create any views or permanent tables and thus reducing our database object footprint.

---

If you are unfamiliar with the functionality of returning a data set in a stored procedure, here is an example in its simplest form (and remember, you can parameterize the stored procedure to add reusability for different reporting needs).

CREATE OR ALTER PROCEDURE spGetEmployees AS 
SET NOCOUNT ON;

SELECT *
FROM   Employees;

RETURN;
GO

The simplest execution is the following:

EXECUTE spGetEmployees;

If you want to store the results of the stored procedure in a table or a table variable we use the following syntax.

DROP TABLE IF EXISTS #TempResults;
CREATE TABLE #TempResults 
(
EmpID INTEGER,
EmpName VARCHAR(100)
);
GO

INSERT INTO #TempResults EXEC spGetEmployees;
GO

SELECT * FROM #TempResults;

And here is the syntax for inserting into a table variable.

DECLARE @vTableVariable TABLE(EmpID INTEGER,EmpName VARCHAR(100));

INSERT @vTableVariable EXEC spGetEmployees;

SELECT * FROM @vTableVariable;

Hope this helps in understanding the benefit of stored procedures that simply return a result set and where they can best be used.

Also, if you haven’t done so, review table-valued functions as well and take some time to understand where to best use tables, views, table-valued functions, and stored procedures that return a result set.

Happy coding!!