Log in  ::  Register  ::  Not logged in

# A Few Mathematical UDFs

By Alexander Chigrik, 2001/10/09

## 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)
Total article views: 5905 | Views in the last 30 days: 1

Related Articles
BLOG

### SQL Server – "Denali" – PERCENT_RANK() Analytic Function

PERCENT_RANK() returns the position of a row within the result set. In contrast to RANK() function, ...

FORUM

### user defined functions & columns

user defined functions & columns

FORUM

### Help with user defined function

returning multiple updateable values from USer Defined function

FORUM

### function to return CW from date

function to CW return wrong number of CW in Access but in management studio return correct number

ARTICLE

### SQL 2000 User Defined Function Primer

Many programming languages have supported User Defined Functions for years, but they are new to SQL ...

Tags
 t-sql user defined function (udf)

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

Email address:

#### You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

### Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.