Binaryworld - Mathematical User-Defined Functions ... [ SQL Server -> User Defined Function ]

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)