Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Few Mathematical UDFs

By Alexander Chigrik,



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: 5911 | Views in the last 30 days: 2
 
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
 
Contribute

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?
Steve Jones