SQL Tips - Mathematical User-Defined Functions

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.

Factorial

Returns the factorial value of the given number.

Syntax

Factorial ( number )

Arguments

number - 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)

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)

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 - 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)

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 - 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)

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 - integer value.
n - 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)

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 - integer value.
n - 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)

Here is the result set:

-----------
10

(1 row(s) affected)