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.
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)
13 Comments. Leave new
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.
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.
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());
cannot return varchar values! Conversion failed when converting the varchar value ‘…’ to data type int. Severity 16
sir, can “FindArea” procedure return a table?
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
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?
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
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,”‘,'”) + “)’)”
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.
How do we insert values into two different tables in the same database using stored procedures
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
Each connection can run a stored procedure.