Dynamic Select Statement in User Defined Function (UDF)

I have 2 tables in my database, i want to select out the InvoiceID, ClientID and total Amount for the invoice.

I know i can simply sum all the amount in InvoiceItem and Group by  InvoiceID, ClientID to select the data,

but the problem is the amount for the items are differ depending on group.

IF group 1, sum all item under group 1,

if group 2 , sum of group 1 - sum all item under group 2 etc.....

I tried to use User Defined Function (UDF) to calculate the amount per invoice in my select statement, it gave me error:

Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

Above error is cause of I have dynamic select statement in the User Defined Function (UDF).

I know that I cant have dynamic statement in UDF, and I can use if else statement here, but pls imagine I have more than 10 groups of different calculation.

I tried stored procedure, but I cant execute or call the stores procedure in my select statement.

Anyone out there can advise?

Below are the structure of my tables.

Invoice {

 InvoiceID int,

ClientID int

}

InvoiceItem{

 InvoiceID int,

ProductID int,

Price decimal(18,2),

Group int

}

Hi, what is the output that you want? A table result that list the ClientID, InvoiceID, GroupID and TotalForEachGroup? If you need to perform calculations based on individual groups such that Group_2_Total = Group_1_Sum - Group_2_Sum, then probably you have to do it programmatically. Unless in this case, if Group 2 items is a subset of Group 1 and you have a table that stores such relationships.