Recursive user defined function in SQL Server

By | December 13, 2012

Introduction

In this post I will explain how write a recursive user defined function in SQL Server

Description

Recursive function is a function which calls itself for a certain number of times based on some business logic. We will create a simple recursive user defined function for calculating factorial of a number.

Factorial of a number n is calculated by multiplying n with (n-1) till (n-1) is greater than 1. For example, factorial of 5 is calculated as 5*4*3*2.

[sourcecode language=”sql”]
CREATE FUNCTION [dbo].[Factorial](@Num BIGINT)
RETURNS BIGINT
AS
BEGIN
IF @Num>0
BEGIN
IF @Num >=2
SET @Num=@Num * dbo.Factorial(@Num-1)
END
ELSE IF @Num=0
BEGIN
SET @Num=1
END
ELSE
SET @Num=@Num

RETURN @Num
END
[/sourcecode]

In above recursive function we have called same function with decreasing its parameter by 1 while number is greater than or equal to 2.

In SQL Server we can call a function, stored procedure, trigger or views recursively for maximum of 32 times. But in the above Factorial function, even with a BIGINT data type we can calculate factorial of numbers till 20. After 20 it will give arithmetic overflow error

Leave a Reply

Your email address will not be published. Required fields are marked *