Friday, 12 April 2013

function in sql (Table Value)

ALTER FUNCTION [Accounts].[userDefinedFunction_Account_Group_Details]
(   
    @companyId uniqueidentifier,
    @financialyearId uniqueidentifier,
    @fromDate Datetime,
    @toDate Datetime
)
RETURNS @GROUP_TABLE TABLE
(
    GroupId uniqueidentifier,
    GroupName varchar(150),   
    OpeningBalanceDebitAmount decimal(18,2),
    OpeningBalanceCreditAmount decimal(18,2),
    DebitAmount decimal(18,2),
    CreditAmount decimal(18,2)
 )

AS
BEGIN

     INSERT INTO @GROUP_TABLE
        SELECT Id as GroupId,GroupName,ISNULL(Accounts.userDefinedFunction_Account_Group_OpeningBalance_Amount(Id, @companyId, @financialyearId, @fromDate),0)as OpeningBalanceDebitAmount,0 as OpeningBalanceCreditAmount, ISNULL(Accounts.userDefinedFunction_Group_Debit_Amount(Id, @companyId, @financialyearId, @fromDate, @toDate),0) as DebitAmount,ISNULL(Accounts.userDefinedFunction_Group_Credit_Amount(Id, @companyId, @financialyearId, @fromDate, @toDate),0) as CreditAmount  FROM Accounts.LedgerGroups
   RETURN
END

No comments:

Post a Comment