Mathematical User-Defined Functions

Introduction

I would like to write the series of articles about useful User-Defined
Functions grouped by the following categories:

In this article, I wrote some useful Mathematical User-Defined
Functions.

Mathematical UDFs

These scalar User-Defined Functions perform a calculation, usually

based on input values provided as arguments, and return a numeric

value.

To download Mathematical User-Defined Functions click this link:

Download Mathematical UDFs

Factorial

Returns the factorial value of the given number.

Syntax

Factorial ( number )
Arguments

number
Is the integer value.
Return Types

bigint
The function’s text:






CREATE FUNCTION Factorial

  ( @number int )

RETURNS bigint

AS

BEGIN

  DECLARE @i int, @factorial bigint

  IF @number > 20 RETURN 0

  SET @i = 1

  SET @factorial = 1

  WHILE @i <= @number
    BEGIN
      SET @factorial = @factorial * @i
      SET @i = @i + 1
    END
  RETURN @factorial
END
GO





Examples

This example returns the factorial value of the number 5:





SELECT dbo.Factorial(5)

GO






Here is the result set:
——————–

120
(1 row(s) affected)

PercentFrom

Returns the percent of the expression1 in the expression2.

Syntax

PercentFrom ( expression1, expression2 )
Arguments

expression1
Is an expression of the exact numeric or approximate numeric

data type category.
expression2
Is an expression of the exact numeric or approximate numeric

data type category.
Return Types

float
The function’s text:






CREATE FUNCTION PercentFrom

  ( @expression1 SQL_VARIANT,

    @expression2 SQL_VARIANT )

RETURNS float

AS

BEGIN

  RETURN (CAST(@expression1 AS FLOAT)/CAST(@expression2 AS FLOAT)*100)

END

GO






Examples

This example returns the percent of the 137 in the 273:





SELECT dbo.PercentFrom(137, 273)

GO






Here is the result set:
—————————————————–

50.183150183150182
(1 row(s) affected)

PercentValue

Returns the percent’s value from the given expression for the

given percent.

Syntax

PercentValue ( expression, percent )
Arguments

expression
Is an expression of the exact numeric or approximate numeric

data type category.
percent
Is the integer value.
Return Types

float
The function’s text:






CREATE FUNCTION PercentValue

  ( @expression SQL_VARIANT,

    @percent int )

RETURNS float

AS

BEGIN

  RETURN ( CAST(@expression AS FLOAT) / 100 * @percent )

END

GO






Examples

Returns the percent’s value from the number 137 for the percent 11:





SELECT dbo.PercentValue (137, 11)

GO






Here is the result set:
—————————————————–

15.07
(1 row(s) affected)

Degree

Returns the degree for the given number and degree value.

Syntax

Degree ( number, degree )
Arguments

number
Is an expression of the exact numeric or approximate numeric

data type category.
degree
Is the integer value.
Return Types

float
The function’s text:






CREATE FUNCTION Degree

  ( @number SQL_VARIANT,

    @degree int )

RETURNS float

AS

BEGIN

  DECLARE @i int, @res float

  SET @i = 1

  SET @res = 1

  WHILE @i <= @degree
    BEGIN
      SET @res = CAST(@number AS FLOAT) * @res
      SET @i = @i + 1
    END
  RETURN @res
END
GO





Examples

Returns the degree 4 for the number 3:





SELECT dbo.Degree(3, 4)

GO






Here is the result set:
—————————————————–

81.0
(1 row(s) affected)

Allocation

Returns the allocation from the m by n.

Syntax

Allocation ( m, n )
Arguments

m
Is the integer value.
n
Is the integer value.
Return Types

int
The function’s text:






CREATE FUNCTION Allocation

  ( @m int,

    @n int )

RETURNS int

AS

BEGIN

  RETURN (dbo.Factorial(@m)/dbo.Factorial(@m-@n))

END

GO






Examples

Returns the allocation from the 5 by 3:





SELECT dbo.Allocation(5,3)

GO






Here is the result set:
———–

60
(1 row(s) affected)

Combination

Returns the combination from the m by n.

Syntax

Combination ( m, n )
Arguments

m
Is the integer value.
n
Is the integer value.
Return Types

int
The function’s text:






CREATE FUNCTION Combination

  ( @m int,

    @n int )

RETURNS int

AS

BEGIN

  RETURN (dbo.Factorial(@m)/(dbo.Factorial(@m-@n)*dbo.Factorial(@n)))

END

GO






Examples

Returns the combination from the 5 by 3:





SELECT dbo.Combination(5,3)

GO






Here is the result set:
———–

10
(1 row(s) affected)