Sql-server – Passing each value from a temp table to a scalar function and sum values

sql serversql-server-2012t-sql

I am relativity newbie in SQL so please do not judge strictly. I have a table valued function.Within that function I have a this code:

 DECLARE @totalPayment DECIMAL(18,4),
         @totalPaymentConverted DECIMAL(18,4),
         @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 to calculate sum amount and I need to convert it in one currency. I have been told to use APPLY but I cannot understand how to use it in my code. Maybe someone could show in code example?

Best Answer

It is fairly straightforward with scalar functions, although your description is very confusing and hard to follow with regard to function names and parameter names. Anyway, when you have a scalar function and a table of values that you want to pass as arguments to the function one row at a time, you can go like this:

SELECT
  dbo.function_scalar(Amount, CurrencyId, 'EU')
FROM
  @GetTotalPaymentAmount
;

That will give you a column of values each of which is the result for a corresponding row of the table. At this point you can specify Amount and CurrencyId to return them along with the results to verify the results match the input values.

Now, as I have understood from your description, the results will be amounts in the same currency. You can get the total amount by aggregating the column returned by the function, for which you simply need to apply SUM() to the function result:

SELECT
  SUM(dbo.function_scalar(Amount, CurrencyId, 'EU'))
FROM
  @GetTotalPaymentAmount
;

That is basically it. If you need to return the result as a single-row single-column dataset or otherwise use it as a derived table in another query, you will probably want to assign an alias to the aggregated column by changing the SUM line to this:

  TotalAmount = SUM(...)

or this:

  SUM(...) AS TotalAmount

You can also just store the result in a variable if necessary:

  @TotalAmount = SUM(...)
Related Question