# MySQL simple stored procedure calling another simple one and never ending

Trying to calculate the sum of n x k-combinations (ie. non-ordered) for m elements.

To calculate a k-combination, I need to create a function which calculates factorial.

The code is quite simple, and works :

```BEGIN
DECLARE factorial INT DEFAULT 1;
DECLARE counter INT;
SET counter = m;  --  m is my variable
factorial_loop: REPEAT
SET factorial = factorial * counter;
SET counter = counter - 1;
UNTIL counter = 1
END REPEAT;
RETURN factorial;
END
```

Then I want to create anothe simple function adding the k-combinations until a given threshold.

Exemple : I want to know how many k-combinations exist for 7 elements from 4 elements.

It’s :

• the combination of 7 elements within 7 elements = 1 unordered combination
• the combination of 6 elements within 7 elements = 7 unordered combination
• the combination of 5 elements within 7 elements = 21 unordered combination
• the combination of 4 elements within 7 elements = 35 unordered combination

TOTAL = 64

For this I created a second stored procedure, calling the first one.

```BEGIN
DECLARE tempo INT DEFAULT 0;
DECLARE counter2 INT;
SET counter2 = X;  --  X is the number of elements, in the example 7
combisum_loop: REPEAT
SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2));  --  the mathematical formula for k-combinations
SET counter2 = counter2 - 1 ;
UNTIL counter2 = Y  --  Y is the thresold, in the example 4
END REPEAT;
RETURN tempo;
END
```

Problem : calling the second function with even these small numbers never ends. What am I doing wrong ?

PS: some thoughts :

• if I remplace the mathematical function by the number 1, it returns 4 (the correct result)
• if I replace the mathematical formula by a unique call to the factorial function, it works ! (ie. it finishes, but of course that’s not the result I want to achieve)
• if I create a k-combination function with the whole formula but without the loop it also works !!!
• if I call the k-combination function within my loop, it fails

So the problem seems to be when I have both a loop and several (indirect) calls to the – indeed simple – factorial function.

NB: tried to modify the second stored procedure in order to avoid the factorial(0) like suggested by @nbk, but this still fails :

```BEGIN
DECLARE tempo INT DEFAULT 1;
DECLARE counter2 INT;
SET counter2 = X-1;
voila3_loop: REPEAT
SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2));
SET counter2 = counter2 - 1 ;
UNTIL counter2 = Y
END REPEAT;
RETURN tempo;
END
```

the first iteration should be tempo = 1 + factorial(7)/(factorial(6)*factorial(7-6)); and should definitely work

Besides minor problems in your code.

your first round of your second function starts with a divison by NULL

because `factorial(0) =0`

I am unfamiliar with the formula you are using

And so the function doesn’t run at all.

so the formula doesn’t seem to be right.

```CREATE Function factorial(m INT)
RETURNS BIGINT unsigned DETERMINISTIC
BEGIN
DECLARE factorial BIGINT UNSIGNED DEFAULT 1;
DECLARE counter INT;
SET counter = m;  --  m is my variable
factorial_loop: REPEAT
SET factorial = factorial * counter;
SET counter = counter - 1;
UNTIL counter <= 1
END REPEAT;
RETURN factorial;
END
```
```✓
```
```SELECT factorial(4)/(factorial(4) * factorial(0))
```
```| factorial(4)/(factorial(4) * factorial(0)) |
| -----------------------------------------: |
|                                       null |
```
```SELECT factorial(4)/(factorial(3) * factorial(1))
```
```| factorial(4)/(factorial(3) * factorial(1)) |
| -----------------------------------------: |
|                                     4.0000 |
```
```SELECT factorial(4)/(factorial(2) * factorial(2))
```
```| factorial(4)/(factorial(2) * factorial(2)) |
| -----------------------------------------: |
|                                     6.0000 |
```
```SELECT factorial(4)/(factorial(1) * factorial(3))
```
```| factorial(4)/(factorial(1) * factorial(3)) |
| -----------------------------------------: |
|                                     4.0000 |
```
```CREATE Function factor(x INT)
RETURNS BIGINT unsigned DETERMINISTIC
BEGIN
DECLARE tempo BIGINT UNSIGNED  DEFAULT 0;
DECLARE counter2 INT;
SET counter2 = x;  --  X is the number of elements, in the example 7
combisum_loop: REPEAT
SET @a := counter2;
SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2));  --  the mathematical formula for k-combinations

SET counter2 = counter2 - 1 ;
UNTIL counter2 <= Y  --  Y is the thresold, in the example 4
END REPEAT;
RETURN tempo;
END
```
```SELECT factor(4)
```
```SELECT @a
```
```| @a |
| -: |
|  4 |
```

db<>fiddle here