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

This is one of the most asked questions in recent time and the answer is even simpler.

Here is the question – How to Pass One Stored Procedure’s Result as Another Stored Procedure’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 Stored Procedure it is very common requirement that we have need of one stored procedure’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 stored procedure 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 stored procedure (SquareSP) to second stored procedure (FindArea). We can do that by using 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 stored procedure to second procedure.

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

-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO

Click to Download Scripts

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

About these ads

6 thoughts on “SQL SERVER – Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

  1. 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
    
    
    
  2. 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());

  3. cannot return varchar values! Conversion failed when converting the varchar value ‘…’ to data type int. Severity 16

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s