SP Function C#.Net

public DataTable GetFeeDiscloseRecurringFees(string planNumber)

         {

             dtData = new DataTable();

             string query;

             try

             {

                 query = "pr_GetFeeDiscloseRecurringFees";

                 this.DataContext.Connect();

                 this.DataContext.Connection.RefeshParameters();

this.DataContext.Connection.AddParameter("@PlanNumber", _JULY_Library.clsConnection.enmDbType.Integer, planNumber);

dtData = this.DataContext.Connection.GetDataTable(query, CommandType.StoredProcedure, "", true);

                 return dtData;

             }

             catch (Exception ex)

             {

                 throw ex;

             }

         }

USE[TPAManager_be]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[pr_GetFeeDiscloseRecurringFees]

@PlanNumber int

AS

SET NOCOUNT ON

Select Payee as provider,ServiceType, PaymentMethod, PaymentInterval,FeeName

, Case when[AmountType]='Percent'then dbo.fnFormatPercentage([FeeAmount],NoOfDecimals)

ELSE dbo.fnFormatCurrency([FeeAmount],NoOfDecimals) END ASAnnualFeeAmount

, DBO.fnFormatWithUnitTypeInputMast(ISNULL([Quantity], 0),[UnitTypeInputMask],0)AS CurrentQuantity

--Case when FeeID in(22,86) then 0 Else NoOfDecimals End) AS CurrentQuantity

, dbo.fnFormatCurrency([AnnualFee],0) AS ProjectedAnnualFee

--Case when FeeID in(22,86) then 0 Else NoOfDecimals End) AS ProjectedAnnualFee

fromvw_FeeDisclosureRecurringFeeDetails

WHEREPlanNumber  =@PlanNumber

ORDER BYSequenceNumber

ALTER FUNCTION [dbo].[fnFormatWithUnitTypeInputMast](@value Decimal(24, 7), @UnitTypeInputMask varchar(50),@NumberOfDecimals int)

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @temp varchar(38)

SET @value = ISNULL(@value, 0)

IF@UnitTypeInputMask = 'Currency'

                SET@temp = DBO.fnFormatCurrency(@Value,@NumberOfDecimals)

ELSE

                SET@temp = DBO.fnFormatDecimal(@Value, CAST(@UnitTypeInputMask as Int))

RETURN @temp

END

ALTER FUNCTION [dbo].[fnFormatCurrency] (@value Decimal(24, 7),@NumberOfDecimals int)

RETURNS VARCHAR(32)

AS

BEGIN

                DECLARE@temp varchar(38),

                                                @NegYN BIT

               

                SET@value = ISNULL(@value, 0.00)

                IF@value < 0.00

                BEGIN

                                SET @NegYN = 1

                                SET @value = ABS(@VALUE)

                END

                ELSE

                BEGIN

                                SET @NegYN = 0

                END

                IF@NumberOfDecimals > 0

                BEGIN

                                SET @temp = PARSENAME(Convert(varchar,Convert(money,@value),1),2)

                                SET @temp = @temp + RIGHT(str(@value, 32,@NumberOfDecimals) ,@NumberOfDecimals + 1)

                END

                ELSE

                BEGIN

                                SET @temp = PARSENAME(Convert(varchar,Convert(money,cast(round(@Value, 0) as bigint)),1),2)

                END

               

                if@NegYN = 1

                                SET @temp = '($' + @temp + ')'

                ELSE

                                SET @temp = '$' + @temp

                RETURN@temp

END