How to combine multiple stored procedures into a single resulset

Let’s say, you have multiple stored procedures each of which is tied to a partial view or View Component in MVC. Now, while this may seem like a logical thing to do, but what if you want the entire payload to return to a single View.

Well, how about combine all the stored procedures and return a single result set. Here’s how in three simple steps.

First, let’s take a look at the diagram below:

The Employee table has the following records:

Following are the three stored procedures that I have created for the purposes of this demo:

CREATE OR ALTER PROCEDURE [dbo].[spGetMinimumSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN
	SET NOCOUNT ON;
	 
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary BETWEEN @MinSalaryRange AND @MaxSalaryRange)
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMedianSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN	
	SET NOCOUNT ON;
  
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
((Salary > @MinSalaryRange) AND (Salary <= @MaxSalaryRange))
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMaximumSalary]	
	@Salary MONEY	
AS
BEGIN
	SET NOCOUNT ON;
	   
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary > @Salary)
END

Now, comes the interesting part. Let's create a temporary table:

CREATE TABLE #TmpSalary 
(
	FirstName NCHAR(25),
	LastName NCHAR(25),
	Gender NCHAR(10)
)

As you can see, the temporary table only has the structure, but no data:

Select * from #TmpSalary

Now, let's insert the records into the temporary table that are returned from executing the stored procedures like so:

INSERT INTO #TmpSalary
EXEC spGetMinimumSalary 0, 50000

INSERT INTO #TmpSalary
EXEC spGetMedianSalary 50000, 75000

INSERT INTO #TmpSalary
EXEC spGetMaximumSalary 75000

Let's view the records now:

Select * from #TmpSalary

There you have it! 🙂

This entry was posted in SQL Server. Bookmark the permalink.