• Introduction
• Mathematical UDFs
  • Factorial
  • PercentFrom
  • PercentValue
  • Degree
  • Allocation
  • Combination

Introduction

I would like to write the series of articles about useful User-Defined

Functions grouped by the following categories:

• Date and Time User-Defined Functions
• Mathematical User-Defined Functions
• Metadata User-Defined Functions
• Security User-Defined Functions
• String User-Defined Functions
• System User-Defined Functions
• Text and Image User-Defined Functions

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)