stored procedures

hey, Rehaan Ahmed

You can execute one stored procedure into another stored procedure.There is no such restrictions of numbers of stored procedure executed one at a time.

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Singh7y ago
Accepted answer
hey, Rehaan Ahmed

You can execute one stored procedure into another stored procedure.There is no such restrictions of numbers of stored procedure executed one at a time.

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 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
+3
ahmidi mohamed7y ago
in the stored Procuder in Sql

create proc nom_proc( @id int)
as
begin
returns int
select * from pilote where id = @id
-1