Can we execute procedure inside a procedure? Can you create a recursive procedure?

Yes we can execute different /same procedure inside a procedure and pass the parameters and get back the result from the procedure. For the recursive calling of procedure SQL only allow you for the 32 times so you can call it only in case you are confirmed that it will not be executed more than 32 times else you will get the error.

Below is the sample for recursive used for finding the factorial of a number.

CREATE PROCEDURE [dbo].[usp_Get_Factorial]

(

 @P_Number Integer,

 @O_Fact_no Integer OUTPUT

)

AS

 DECLARE @V_In Integer

 DECLARE @V_Out Integer

 IF @P_Number != 1

     BEGIN

     SELECT @V_In = @P_Number - 1

     EXEC usp_Get_Factorial @V_In, @V_Out OUTPUT

     SELECT @O_Fact_no = @P_Number * @V_Out

 END

     ELSE

         BEGIN

             SELECT @O_Fact_no = 1

         END

RETURN

GO

Execution of the procedure will call the procedure recursively.

Declare @O_Fact_no Integer

Exec [dbo].[usp_Get_Factorial] 9,@O_Fact_no output

Select @O_Fact_no