Passing each value from temp table to the scalar function and sum values

Question

I have a table valued function.Within that function i have a this code

 DECLARE @totalPayment DECIMAL(18,4)
 DECLARE @totalPaymentConverted DECIMAL(18,4)
 DECLARE @fromCurrencyIdForPaymentSystemsId CHAR(3)

 DECLARE @GetTotalPaymentAmount AS TABLE 
 (
 Amount     DECIMAL(18,4),
 CurrencyId CHAR(3)
 )

INSERT INTO @GetTotalPaymentAmount
SELECT SUM(Amount),CurrencyId
FROM [dbo].[fn_DepositWithdrawReport]()
WHERE OperationTypeId = 2
GROUP BY CurrencyId

This insert return met table like this

 A      C
-----|------
550  |USD
650  |EU
     |

I want to pass each of this values Amount and CurrencyId to the Scalar valued Function as Input parameter like this

[dbo].[function_Valued](@totalPayment,@fromCurencyId,@toCurrencyId)

And sum that values for instance if we consider the table above

[dbo].[functin_scalar](550,USD,EU)=450
[dbo].[function_scalar](650,EU,EU)=650
Get total 1100

Put simply i want calculate sum amount and i need to convert it in one currency.

You need [APPLY](https://technet.microsoft.com/nl-nl/library/ms175156(v=sql.105).aspx) — HoneyBadger, Jan 26 '18 at 11:57
The link I posted has all the information you need (even a nice example) — HoneyBadger, Jan 26 '18 at 11:58

score 2 · Accepted Answer · answered Jan 26 '18 at 17:04

First you need to read the answer of this post to get more about the differences between Table Valued Function and Scalar function, which will help you to get more understanding on which method will work best in your situation.

Secondly, I'll post a simple scalar function and you need to modify it according to your needs:

CREATE FUNCTION TotalPayment 
(
    @Amount         DECIMAL(18,4), 
    @FromCurrency   CHAR(3),
    @ToCurrency     CHAR(3)
)
RETURNS DECIMAL(18,4)
AS
BEGIN

    IF(@FromCurrency = 'USD' AND @ToCurrency = 'EUR')
    SET @Amount = @Amount * 0.803743

    IF(@FromCurrency = 'EUR' AND @ToCurrency = 'USD')
    SET @Amount = @Amount * 1.24418

    RETURN @Amount
END
GO

The function takes the amount with EUR as base currency, you could change that into your preferred currency. Also, you need to maintain the currency rate, as the market goes up and down in this matter, so you need to figure out a way to update the currency rate regularly . Unless your database has that data. If so, you can just select the rates from its table.

Hint: declaring a new @param to get currency rate from another table will be useful.

Then, you can use a simple SELECT statement

IF you need to convert all rows to EUR :

SELECT
    Amount, 
    Currencyid, 
    dbo.TotalPayment(Amount, CurrencyId, 'EUR') AS ConvertedToEUR
FROM  GetTotalPaymentAmount

IF you want to get only the total amount (after converted):

SELECT SUM(dbo.TotalPayment(Amount, CurrencyId, 'EUR')) AS TotalinEUR
FROM  GetTotalPaymentAmount

score 0 · Answer 2 · answered Jan 26 '18 at 12:04

0

Could you do this (at the end) :-

SELECT [dbo].[function_scalar](TheSum, CurrencyId,EU) AS GrandTotal
FROM (
SELECT SUM(Amount) AS TheSum, CurrencyId
FROM [dbo].[fn_DepositWithdrawReport]()
WHERE OperationTypeId = 2
GROUP BY CurrencyId)

answered Jan 26 '18 at 12:04

corky

1
1

Getting error Invalid column name in variables TheSum and currencyid – So_oP Jan 26 '18 at 12:25
Does the sub-query run? SELECT SUM(Amount) AS TheSum, CurrencyId FROM [dbo].[fn_DepositWithdrawReport]() WHERE OperationTypeId = 2 GROUP BY CurrencyId – corky Feb 05 '18 at 16:08

Passing each value from temp table to the scalar function and sum values

2 Answers2