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 AS AnnualFeeAmount

, 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

from vw_FeeDisclosureRecurringFeeDetails

WHERE PlanNumber  = @PlanNumber

ORDER BY SequenceNumber

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