存储过程中的递归 [英] Recursion in Stored Procedures

问题描述

如何在存储过程中使用递归

解决方案

检查此



T-SQL中的递归 [ ^ ]

< b>创建存储过程

 创建  PROCEDURE  Factorial_ap 
  @ Number 整数，
  @RetVal 整数输出 
  AS  
  BEGIN  
  DECLARE   @ In 整数， @ Out 整数
  IF   @ Number ！=  1  
  BEGIN  
  SELECT   @ In  =  @ Number-1  
  EXEC  Factorial_ap  @ In ， @ Out  输出 
  SELECT   @ RetVal  =  @ Number  *  @ Out  
  END  
  ELSE  
  BEGIN  
  SELECT   @ RetVal  =  1  
 < span class =code-keyword> END  
  RETURN   @ RetVal  
  END  
  GO  

致电存储过程

  DECLARE   @ RESULT 整数
  SET   @ RESULT  =  0  
  EXEC  Factorial_ap  5 ， @ RESULT  < span class =code-keyword> OUTPUT  
  PRINT   @ RESULT  

可以在中使用递归公用表格表达式 [ ^ ]。

;  WITH  MyCTE  AS  
（
 < span class =code-comment>  -   初始值 
  SELECT   1   AS  ID 
  UNION   ALL  
   -   递归部分 
  SELECT  ID +  1   AS  ID 
  FROM  MyCTE 
  WHERE  ID< 100 
） 
  SELECT  ID 
  FROM  MyCTE 

How to use recursion in Stored Procedures

解决方案

Check this

Recursion in T–SQL[^]

Create Stored Procedure

CREATE PROCEDURE Factorial_ap
@Number Integer,
@RetVal Integer OUTPUT
AS
BEGIN
DECLARE @In Integer, @Out Integer
IF @Number != 1
BEGIN 
SELECT @In = @Number-1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END	
RETURN @RetVal	
END
GO

Call the Stored Procedure

DECLARE @RESULT Integer
SET @RESULT = 0
EXEC Factorial_ap 5, @RESULT OUTPUT
PRINT @RESULT

It is possible to use recursion with Common Table Expression[^].

;WITH MyCTE AS
(
    --initial value
    SELECT 1 AS ID
    UNION ALL
    --recursive part 
    SELECT ID + 1 AS ID
    FROM MyCTE
    WHERE ID<100
)
SELECT ID
FROM MyCTE

这篇关于存储过程中的递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！