
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Mathematical User-Defined Functions

Alexander Chigrik
chigrik@mssqlcity.com

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)

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc