Example of recursive calling stored procedure in SQL Server

Time:2021-4-14

Recursion refers to the situation in which a code fragment calls itself; the danger lies in how to prevent it from calling itself repeatedly if it calls itself once. That is to say, recursion test is provided to ensure that it can jump out at the right time.

Take hierarchy as an example to explain recursive calls in stored procedures.

recursion


CREATE PROC [dbo].[usp_spFactorial]
@InputValue INT,
@OuputValue INT OUTPUT
AS
BEGIN
   DECLARE @InValue  INT;
   DECLARE @OutValue  INT;
    IF(@InputValue!=1)
      BEGIN
         SET @InValue = @InputValue - 1;
         EXEC spFactorial @InValue,@OutValue OUTPUT;
         SELECT @OuputValue = @InputValue * @OutValue;
      END
    ELSE
      BEGIN
      SET @OuputValue = 1;
      END
END

When you create this stored procedure, you will encounter a report message