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

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)

, ,
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #023
Next Post
SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Related Posts

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
    
    
    
    Reply
  • 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
  • cannot return varchar values! Conversion failed when converting the varchar value ‘…’ to data type int. Severity 16

    Reply
  • sir, can “FindArea” procedure return a table?

    Reply
  • 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
  • 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
  • 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
  • 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
    • 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
  • 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

Leave a Reply

Menu