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:
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)