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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 9, 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
  • 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)


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    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