MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES SQL Scripts & Samples Tips » Database Forum » Slideshows
 Database Journal |DBA Support |SQLCourse |SQLCourse2

## MS SQL

Posted Jan 10, 2001

# Mathematical User-Defined Functions

By Alexander Chigrik

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
• Security User-Defined Functions
• String User-Defined Functions
• System User-Defined Functions
• Text and Image 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

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)

```

MS SQL Archives

 Latest Forum Threads MS SQL Forum Topic By Replies Updated SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM Need help changing table contents nkawtg 1 August 17th, 03:02 AM SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM SQL Server – Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM