In some cases, I like to use SQL Server Functions to encapsulate some of my SQL Server logic into reusable and maintainable components.

Recently, I have found the use of Table-valued Functions to be extremely helpful.  With Table-valued functions, you can return multiple values, which is useful enough in itself, or even entire result sets.

The following is an example of creating a simple Table-valued Function that prorates a monthly amount based on the number of days which have elapsed.  Notice that the return value is a TABLE variable.

CREATE FUNCTION [dbo].[fnProrateMonthlyAmount]
(
    @Month datetime,            — month for which the proration will be calculated
    @DurationStart datetime,    — start date of the proration duration
    @DurationEnd datetime,      — end date of the proration duration
    @MonthlyAmount money,       — monthly amount to be prorated
    @MonthlyHours money         — monthly hours to be prorated
)

RETURNS @RtnValue table
(
    TotalDays int,             — total days in month
    TotalAmount money,         — total monthly amount
    ProratedDays int,          — number of prorated days
    HoursPerDay money,         — number of hours per day
    ProratedHours money,       — number of prorated hours
    AmountPerDay money,        — dollar amount per day
    ProratedAmount money       — prorated dollar amount
)
AS 
BEGIN

    — declare variables
    DECLARE @MonthEnd datetime
    DECLARE @TotalDays int, @ProratedDays int
    DECLARE @HoursPerDay money, @ProratedHours money
    DECLARE @AmountPerDay money, @ProratedAmount money
   
    — initialize variables
    SET @Month = DATEADD(DAY, -(DATEPART(DAY, @Month)-1), @Month)   
    SET @MonthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, @Month))
    SET @TotalDays = DATEPART(dd, @MonthEnd)
    SET @ProratedDays = DATEDIFF(DAY, @DurationStart, @DurationEnd) + 1
    SET @AmountPerDay = ROUND(@MonthlyAmount/@TotalDays, 2)
    SET @HoursPerDay = ROUND(@MonthlyHours/@TotalDays, 2)
    SET @ProratedAmount = @MonthlyAmount
    SET @ProratedHours = @MonthlyHours
    IF @TotalDays > @ProratedDays
    BEGIN
        SET @ProratedAmount = @ProratedDays * @AmountPerDay
        SET @ProratedHours = @ProratedDays * @HoursPerDay
    END
   
    — return results
    INSERT INTO @RtnValue
    (
        TotalDays,
        TotalAmount,
        ProratedDays,
        HoursPerDay,
        ProratedHours,
        AmountPerDay,
        ProratedAmount
    )
    SELECT    
        @TotalDays,
        @MonthlyAmount,
        @ProratedDays,
        @HoursPerDay,
        @ProratedHours,
        @AmountPerDay,
        @ProratedAmount
   
    Return
END

 

I can simply render the results of the function like this:

SELECT * FROM
dbo.fnProrateMonthlyAmount
(
    ‘7/1/2012’,    
    ‘7/1/2012’,
    ‘7/31/2012’,
    10000,
    50
)

Which gives me this output:

image

Or I can stuff the results of the function into a TABLE variable or SQL TYPE:

DECLARE @prorate TABLE
    (
        TotalDays int,
        TotalAmount money,
        ProratedDays int,
        HoursPerDay money,
        ProratedHours money,
        AmountPerDay money,
        ProratedAmount money
    )

INSERT INTO @prorate
    (
        TotalDays,
        TotalAmount,
        ProratedDays,
        HoursPerDay,
        ProratedHours,
        AmountPerDay,
        ProratedAmount
    )
    [dbo].[fnManagedServicesProrateAmount](@Month, @DurationStart, @DurationEnd, @MonthlyRevenue, @MonthlyHours)

 

And if I know that the results are a single record with multiple values, and I want to work with these values to further my calculations, I can stuff the result directly into variables:

SELECT TOP 1
    @TotalDays = TotalDays,
    @TotalAmount = TotalAmount,
    @ProratedDays = ProratedDays,
    @HoursPerDay = HoursPerDay,
    @ProratedHours = ProratedHours,
    @AmountPerDay = AmountPerDay,
    @ProratedAmount = ProratedAmount
FROM
    [dbo].[fnManagedServicesProrateAmount](@Month, @DurationS, @DurationEnd, @MonthlyRevenue, @MonthlyHours)