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 (http://blog.sqlauthority.com)