Flexable Rate Recursion

DECLARE @Customer table
(
 Cust_ID int,
 Cust_F_Name varchar(60),
 Cust_M_Name varchar(40),
 Cust_L_Name varchar(60),
 Base_Limit decimal(9, 2)
)

DECLARE @RateSet table
(
 Cust_ID int,
 Annual_Rate decimal(8, 4),
 Service_Years int
)

INSERT INTO @Customer
 SELECT
 1 AS Cust_ID,
 'Joe' AS Cust_F_Name,
 'Cig' AS Cust_M_Name,
 'Camel' AS Cust_L_Name,
 35540.00 AS Base_Limit
 UNION
 SELECT
 2 AS Cust_ID,
 'John' AS Cust_F_Name,
 'Who' AS Cust_M_Name,
 'Doe' AS Cust_L_Name,
 19500.50 AS Base_Limit
 UNION
 SELECT
 3 AS Cust_ID,
 'Jane' AS Cust_F_Name,
 'Who' AS Cust_M_Name,
 'Doe' AS Cust_L_Name,
 56560.49 AS Base_Limit
 UNION
 SELECT
 4 AS Cust_ID,
 'Mini' AS Cust_F_Name,
 'Earner' AS Cust_M_Name,
 'Wage' AS Cust_L_Name,
 5.01 AS Base_Limit

INSERT INTO @RateSet
 SELECT
 1 AS Cust_ID,
 1.0450 AS Annual_Rate,
 12 AS Service_Years
 UNION
 SELECT
 2 AS Cust_ID,
 0.9412 AS Annual_Rate,
 5 AS Service_Years
 UNION
 SELECT
 3 AS Cust_ID,
 1.2500 AS Annual_Rate,
 18 AS Service_Years
 UNION
 SELECT
 4 AS Cust_ID,
 2.8021 AS Annual_Rate,
 14 AS Service_Years

SELECT
  cst.Cust_ID,
  (cst.Cust_L_Name + ', ' + cst.Cust_F_Name + ', ' + cst.Cust_M_Name) AS Cust_Name,
  cst.Base_Limit,
  rts.Annual_Rate,
  rts.Service_Years,
  dbo.fn_RecurseM( cst.Base_Limit, rts.Annual_Rate, rts.Service_Years) AS Final_Limit
FROM @Customer AS cst
JOIN @RateSet AS rts ON
  cst.Cust_ID = rts.Cust_ID

;WITH
  CustList AS
  (
    SELECT
      cst.Cust_ID,
      (cst.Cust_L_Name + ', ' + cst.Cust_F_Name + ', ' + cst.Cust_M_Name) AS Cust_Name,
      cst.Base_Limit,
      rts.Annual_Rate,
      1 AS Proc_Year,
      rts.Service_Years,
      dbo.fn_RecurseM( cst.Base_Limit, rts.Annual_Rate, 1) AS Limit_Amount
    FROM @Customer AS cst
    JOIN @RateSet AS rts ON
      cst.Cust_ID = rts.Cust_ID
    UNION ALL
    SELECT
      cl.Cust_ID,
      cl.Cust_Name,
      cl.Base_Limit,
      cl.Annual_Rate,
      (cl.Proc_Year + 1) AS Proc_Year,
      cl.Service_Years,
      dbo.fn_RecurseM( cl.Base_Limit, cl.Annual_Rate, (cl.Proc_Year + 1)) AS Limit_Amount
    FROM CustList AS cl
    WHERE
      cl.Proc_Year < cl.Service_Years
  )
  SELECT
    Cust_ID,
    Cust_Name,
    Base_Limit,
    Annual_Rate,
    Proc_Year,
    Service_Years,
    CAST(Limit_Amount AS decimal(9, 2)) AS Limit_Amount
  FROM CustList
  ORDER BY
    Cust_ID,
    Proc_Year

/*
 
 This recursion function was written to help load a table of rates for
 a number of employees. Each one had their own starting amount.
 Each one had their own set number of years available.
 Each one had their own expected yearly increase.
 
*/
/* ***** Object: UserDefinedFunction [dbo].[fn_RecurseM] Script Date: 2/13/2009 12:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_RecurseM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[fn_RecurseM]
GO

/* ***** Object: UserDefinedFunction [dbo].[fn_RecurseM] Script Date: 2/13/2009 12:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fn_RecurseM]
 (
 @Base decimal(32, 6),
 @Multiplier decimal(32, 6),
 @Times int
 )
 
 RETURNS decimal(32, 6)
 AS
 BEGIN

 DECLARE @Total decimal(32, 6)
 DECLARE @RLoop int
 DECLARE @SLoop int
 
 /*
 
 Clear and pre-set all internal variables.
 
 */ 
 SELECT
 @Total = 0.0,
 @RLoop = 0,
 @SLoop = 0
 
 /*
 
 Check if @Times is negative. This is an invalid value at this time.
 
 */ 
 IF @Times < 0
 BEGIN
 SELECT
 @Times = 0
 END
 
 /*
 
 Check if @Times is greater than 30.
 This is to keep the recursive function from
 erroring out due to too many recursions.
 Set up a secondary recursion loop if needed.
 
 */ 
 IF @Times > 30
 BEGIN
 SELECT
 @RLoop = ROUND((@Times / 30), 0, 1),
 @Times = (@Times % 30)
 SELECT
 @SLoop = @RLoop
 END
 
 /*
 
 This loop will be performed at least once.
 
 */ 
 WHILE @RLoop >= 0
 BEGIN
 
 /*
 
 Check if the recursion is split into multiple sets.
 Call the recursion function for each set. The full
 set will be completed before returning to the base call.
 
 */ 
 IF @Times > 0
 BEGIN
 SELECT
 @Total = dbo.fn_RecurseM(@Base, @Multiplier, (@Times - 1)) * @Multiplier
 
 /*
 
 Check if the total returned would exceed the highest value allowed when multiplied again.
 This check is needed within the recursion set loop. An invalid value is set to 0.0.
 This will keep potential errors from happening due to excessively large numbers.
 
 */ 
 IF (@Total * @Multiplier) > 999999999999999999999999.0
 BEGIN
 SELECT
 @Total = 0.0
 END
 
 /*
 
 Check if there is another recursion set loop.
 If so, then set the base to the current total. This will
 allow the recursion to continue properly.
 
 */ 
 IF @RLoop > 0
 BEGIN
 SELECT
 @Base = @Total
 END
 END
 ELSE
 
 /*
 
 This is the end of the recursion set. The base becomes the total as the
 first iteration needs the initial value as the first returned value.
 This value will be iteratively multiplied by the multiplier as each
 successive recursion returns back towards the root call.
 
 */ 
 BEGIN
 SELECT
 @Total = @Base
 END
 
 /*
 
 The end of the recursion set loop. Reduce the recursion set by 1.
 If another set is needed, then reset the recursion counter to 30.
 This is a hard-coded value as when this point is reached the first time,
 the less than 30 set has already been finished. When the recursion loop
 variable falls below 0 then the recursion set loop is done.
 
 */ 
 SELECT
 @RLoop = @RLoop - 1
 IF @RLoop >= 0
 BEGIN
 SELECT
 @Times = 30
 END
 END
 
 /*
 
 Return the new result after multiplied by the multiplier for the number of
 times needed.
 
 */ 
 RETURN(@Total)
 END
GO