Using recursion in a stored procedure in SQL?

Question

I am going to find gcd of two integer numbers by using SQL stored procedure with recursion query. But I got stuck in a loop and I really don't know why, here is my code:

create or alter procedure sp_gcd 
    @s1 int, @s2 int
as
begin
    if (@s1 % @s2 = 0) 
        print @s2
    else if (@s2 % @s1 = 0) 
        print @s1
    else
        begin           
            while (@s2 % @s1 != 0)
                begin
                     declare @temp int = @s2 % @s1 
                     exec sp_gcd @temp, @s1
                end
            print @s1
        end     
 end
 exec sp_gcd 15,21

And this is the result:

I just start working with SQL so I will very much appreciate if anyone could help, thanks a lot.

Side note (if this is for SQL Server): you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Mar 28, 2021 at 6:39

Summer · Accepted Answer · 2021-03-28 03:51:12Z

1

stack calls:

sp_gcd 15 21
sp_gcd 6 15
sp_gcd 3 6
print 3
sp_gcd 3 6
print 3
sp_gcd 3 6
print 3

The reason for this to happen is that the while loop, inside the procedure call sp_gcd 6 15, never reaches the false condition; that is, the condition 15 % 6!=0 is always true.

answered Mar 28, 2021 at 3:51

Summer

4432 gold badges8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using recursion in a stored procedure in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related