Execute the following T-SQL scripts in Management Studio Query Editor to demonstrate the creation and testing of a function to return the minimum of two numbers.

-- SQL minimum of two numbers - money data type

USE AdventureWorks2008;

GO

-- T-SQL user-defined function - scalar function

-- SQL calculate the minimum of two values

CREATE FUNCTION fnMinValue

               (@ColumnA MONEY,

                @ColumnB MONEY)

RETURNS MONEY

AS

  BEGIN

    RETURN (0.5 * ((@ColumnA + @ColumnB) - abs(@ColumnA - @ColumnB)))

  END

GO

-- Test user-defined mininum function - UDF

SELECT dbo.fnMinValue (10.0, 10.0)        -- 10.00

SELECT dbo.fnMinValue (10.0, 12.0)        -- 10.00

SELECT dbo.fnMinValue (12.0, 10.0)        -- 10.00

SELECT dbo.fnMinValue (10.001, 10.002)    -- 10.001

SELECT dbo.fnMinValue (-12.0, -10.0)      -- -12.00

GO

-- Select the highest amount of tax and freight

SELECT   TOP ( 10 ) PurchaseOrderID,

                              TaxAmt,

                    Freight,

                    Minimum = dbo.fnMinValue(TaxAmt, Freight)

FROM     Purchasing.PurchaseOrderHeader

ORDER BY NEWID()

GO

/* Results

PurchaseOrderID   TaxAmt      Freight     Minimum

1857              1724.646    538.9519    538.9519

33                33.6924     10.5289     10.5289

671               3027.486    946.0894    946.0894

74                3567.564    1114.8638   1114.8638

939               12.0632     3.7698      3.7698

3512              19.2578     6.0181      6.0181

2762              1093.554    341.7356    341.7356

185               84.4452     26.3891     26.3891

637               2098.404    655.7513    655.7513

2790              2255.946    704.9831    704.9831

*/

------------