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)

Advertisements