Click here to Skip to main content
15,849,180 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
How to use recursion in Stored Procedures
Posted

Check this

Recursion in T–SQL[^]
 
Share this answer
 
Comments
Debopam Pal 28-Nov-13 1:28am    
The related file to this link cannot download...Please do something.
Maciej Los 28-Nov-13 11:15am    
What do you want to download?
Thanks7872 30-Nov-13 6:23am    
Don't you see the URL at your browser's address bar? Its of Microsoft. What do you mean by do something? He can not do anything.
Debopam Pal 30-Nov-13 6:41am    
Then he should delete the URL and refer for another URL where readers can download the associated zip file.
Thanks7872 30-Nov-13 6:45am    
Why? Forget about download. What about that valuable reading? This link need no change. It is not global rule that any material should have zip file associated with it.
Create Stored Procedure

SQL
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


SQL
DECLARE @RESULT Integer
SET @RESULT = 0
EXEC Factorial_ap 5, @RESULT OUTPUT
PRINT @RESULT
 
Share this answer
 
Comments
Maciej Los 28-Nov-13 11:48am    
Who vote 1? Why?

Good answer, a 5!
It is possible to use recursion with Common Table Expression[^].

SQL
;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
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900