SQL SERVER – Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

April 7, 2013

This is one of the most asked questions related to stored procedure (SP) in recent time and the answer is even simpler. Here is the question – How to Pass One SP’s Result as Another SP’s Parameter. Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code. When we have almost all of our code in SP it is very common requirement that we have need of one SP’s result to be passed as another stored procedure’s parameter.

Let us try to understand this with a simple example. Please note that this is a simple example, the matter of the fact, we can do the task of these two SPs in a single SP but our goal of this blog post is to understand how we can pass the result of one SP to another SP as a parameter.

Let us first create one Stored Procedure, which gives us square of the passed parameter.

-- First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam
-- Additional Code
RETURN (@MyFirstParamSquare)
GO

Now let us create second Stored Procedure which gives us area of the circle.

-- Second Stored Procedure
CREATE PROCEDURE FindArea
@SquaredParam INT
AS
DECLARE @AreaofCircle FLOAT
SELECT @AreaofCircle = @SquaredParam * PI()
RETURN (@AreaofCircle)
GO

You can clearly see that we need to pass the result of the first procedure (SquareSP) to the second procedure (Find Area). We can do that by using the following method:

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
DECLARE @ParamtoPass INT, @CircleArea FLOAT
-- First SP
EXEC @ParamtoPass = SquareSP 5
-- Second SP
EXEC @CircleArea = FindArea @ParamtoPass
SELECT @CircleArea FinalArea
GO

You can see that it is extremely simple to pass the result of the first procedure to second procedure.

SQL SERVER - Pass One Stored Procedure's Result as Another Stored Procedure's Parameter spparamsp-800x202

You can clean up the code by running the following code.

-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO

Let me know what you think of this blog post, I personally thought this problem was very simple, but not many people know the solution of the same. If you find it interesting and if you are using this kind of solution in your environment, I encourage you to share the knowledge with everyone by leaving a comment as a blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Stored Procedure

SQL SERVER – Weekly Series – Memory Lane – #023

SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

SQL SERVER- New DMF in SQL Server 2017 – sys.dm_os_file_exists. A Replacement of xp_fileexist

November 4, 2017

Interview Question of the Week #016 – How to Take Database Offline

April 19, 2015

SQL Server – PowerShell Script – Getting Properties and Details

December 22, 2016

13 Comments. Leave new

Marc Jellinek

April 8, 2013 3:33 am

This is the simplest possible case. But:

Stored procedure return values are generally used to indicate success (0) or failure (other than zero).

If you are going to use the style of programming used in the example, it would be better to use a Function rather than a stored procedure. The return value of a function is expected to be the result, not an indicator of success or failure.

The example only works for scalar results (a single value) of type [int]. Stored procedure return values are only allowed to be integers.

There are two cases for returning values from a stored procedure that should be addressed: scalar values and tables. There is a third (multiple result sets), but those are NOT easily addressed.

/*	PREP */
IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_Square'
	)	DROP PROCEDURE [dbo].[usp_Square];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_FindArea'
	)	DROP PROCEDURE [dbo].[usp_FindArea];
GO

CREATE PROCEDURE [dbo].[usp_Square]
	(	@MyFirstParam	[int],
		@SquareValue	[int]	OUTPUT	
	)
AS
	BEGIN
		DECLARE @return [int] = 0;
		
		BEGIN TRY
			SET @SquareValue = @MyFirstParam * @MyFirstParam;
		END TRY
		BEGIN CATCH
			SET @SquareValue = -1;
			SET @return = ERROR_NUMBER();
		END CATCH

		RETURN @return;
	END
GO

CREATE PROCEDURE [dbo].[usp_FindArea]
	(	@SquaredParam	[int], 
		@AreaOfCircle	[float]	OUTPUT
	)
AS
	BEGIN
		DECLARE @return [int] = 0;
		
		BEGIN TRY
			SET @AreaOfCircle = @SquaredParam * PI();
		END TRY
		BEGIN CATCH
			SET @AreaOfCircle = -1
			SET @return = ERROR_NUMBER();
		END CATCH

		RETURN @return
	END
GO


/*	Use	*/
DECLARE @SquareValue	[int];
DECLARE @CircleArea		[float];

EXEC [dbo].[usp_Square] 
	@MyFirstParam		= 4, 
	@SquareValue	= @SquareValue OUTPUT;

EXEC [dbo].[usp_FindArea]
	@SquaredParam	= @SquareValue, 
	@AreaOfCircle	= @CircleArea	OUTPUT;

SELECT	@SquareValue, @CircleArea

/*	Cleanup */
IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_Square'
	)	DROP PROCEDURE [dbo].[usp_Square];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_FindArea'
	)	DROP PROCEDURE [dbo].[usp_FindArea];
GO

Because a stored proc can have multiple output parameters, multiple values can be passed from the stored proc.

Now for the hard part: passing the results of a stored proc when that result is a table. For this, you need a user-defined table type. Not so much for the source procedure, but so you can easily pass in the results to the target procedure.

Let’s say we have a procedure that lists the databases in an instance of SQL Server (or the databases that the user has access to based on [master].[sys].[databases]) and we want to pass that list into a second procedure.

First, create a user-defined table-type, then two procs, one that lists the databases and the next that adds a column to the list. You’ll catch the output of the first proc and feed it to the second.

/*	PREP */
IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_DatabaseList'
	)	DROP PROCEDURE [dbo].[usp_DatabaseList];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_ProcessDatabaseList'
	)	DROP PROCEDURE [dbo].[usp_ProcessDatabaseList];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[types] [typ] ON [sch].[schema_id] = [typ].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[typ].[name]	= 'id_sysname'
	)	DROP TYPE [dbo].[id_sysname];
GO



CREATE TYPE [dbo].[id_sysname]
AS TABLE
	(
		[id]	[int], 
		[name]	[sysname]
	);
GO

CREATE PROCEDURE [dbo].[usp_DatabaseList]
AS
	BEGIN
		SELECT	[dbs].[database_id], 
				[dbs].[name]
		FROM	[master].[sys].[databases] [dbs];
	END
GO

CREATE PROCEDURE [dbo].[usp_ProcessDatabaseList]
	(
		@database_list	[dbo].[id_sysname]	READONLY
	)
AS
	BEGIN
		SELECT	[dbl].[id], [dbl].[name], CAST('output from second proc' as [varchar](max)) as [msg]
		FROM	@database_list [dbl];
	END
GO

/*	USE	*/
DECLARE @database_list [dbo].[id_sysname]

INSERT INTO @database_list([id], [name])
EXEC [dbo].[usp_DatabaseList]

EXEC [dbo].[usp_ProcessDatabaseList]
	@database_list = @database_list

/*	CLEANUP	*/
IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_DatabaseList'
	)	DROP PROCEDURE [dbo].[usp_DatabaseList];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[objects] [o] ON [sch].[schema_id] = [o].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[o].[name]		= 'usp_ProcessDatabaseList'
	)	DROP PROCEDURE [dbo].[usp_ProcessDatabaseList];
GO

IF EXISTS 
	(	SELECT * 
		FROM	[sys].[schemas] [sch] 
				INNER JOIN [sys].[types] [typ] ON [sch].[schema_id] = [typ].[schema_id]
		WHERE	[sch].[name]	= 'dbo' AND
				[typ].[name]	= 'id_sysname'
	)	DROP TYPE [dbo].[id_sysname];
GO

Joe Celko
April 9, 2013 9:21 pm
Not need to create local variables:
— First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INTEGER
AS
RETURN (@MyFirstParam*@MyFirstParam);
CREATE PROCEDURE FindArea
@SquaredParam INTEGER
AS
RETURN (@SquaredParam * PI());
Reply
shuvo
June 12, 2013 11:16 am
cannot return varchar values! Conversion failed when converting the varchar value ‘…’ to data type int. Severity 16
Reply
Sachu
November 12, 2013 7:29 pm
sir, can “FindArea” procedure return a table?
Reply
Elamparithi.C
November 28, 2013 11:36 pm
The below example will return table and inserted into another table using nested procedure.
SP 1 :
———–
ALTER PROCEDURE [dbo].[CustOrderHist_Elam_Out]
AS
begin
if object_id(‘temp_parithi’) is not null
select ‘table created already’
else
create table temp_parithi(ProductName nvarchar(50),Total nvarchar(50))
declare @temp_parithi table(ProductName nvarchar(50),Total nvarchar(50))
insert into @temp_parithi exec CustOrderHist_Elam_In ‘FRANK’
insert into temp_parithi select * from @temp_parithi
End
SP 2 :
———-
ALTER PROCEDURE [dbo].[CustOrderHist_Elam_In] @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName
When you excute CustOrderHist_Elam_Out:
The following steps are :
1.trigger the SP- CustOrderHist_Elam_In
2.output table will be handle inside the CustOrderHist_Elam_Out and inserted into another table
Reply
SQLnbe
August 29, 2014 5:09 am
Are there any knows issues when calling a stored procedure from within a stored procedure or is this a common practice and SQL doesn’t care?
Reply
Gerry
October 30, 2014 8:01 am
Thanks Pinal for an excellent structure you originally suggested with this thread. I was able to use this same logic to assure the first stored procedure was complete before the second procedure began. For my application, procedure 2 (and 3 and 4) used the output from procedure 1, but the main calling procedure executed procedures 3-4 before procedure 1 was completed. Hence, I received no output. The input and return parameters were irrelevant, but the logic you proposed generated the delay needed to attain the results. Again, thanks for your clear examples from a novice sql coder. Gerry
Reply
Reshma
November 6, 2014 6:37 am
Can anyone help me on how to pass multivalue parameter with call in MySQL in to a ssrs report.
I have a sp in MySQL and am trying to use that sp in SSRS reporting using a call statement in the dataset. It works fine when I try to pass a single value parameter but it doesn’t work for multivalue parameter .
The expression am using is :
“call `sp_bi_incident_Summary` (‘(” + join(Parameters!RegionParam.Value,”‘,'”) + “)’,'(” + join(Parameters!DivisionParam.Value,”‘,'”) + “)’)”
Reply
- Nav
  December 18, 2015 5:31 pm
  Hi Reshma, Did you find a way to resolve this issue ? I am trying to do the same i.e. Trying to call a MySQL Proc in SSRS. Any suggestions will be appreciated. Thanks in advance.
  Reply
Daniel
April 18, 2016 6:49 pm
How do we insert values into two different tables in the same database using stored procedures
Reply
Bradley L Johnson
December 23, 2017 12:17 am
My question is about how the stored procedures are processed. Do they process serially or in parallel? Is it possible to have one stored procedure start multiple stored procedures all at once or will the first stored procedure have to finish before the second starts? Thanks
Reply
- Pinal Dave
  December 25, 2017 6:52 am
  Each connection can run a stored procedure.
  Reply