One of the databases that I support reports all money values in both the local currency and US Dollars. Very often we will do all of our calculations in a table variable using the local currency and then calculate USD equivalents at the very end.
With this methodology, adding a new money field to a table variable requires making four code changes – adding the local money field, adding the USD money field, writing the code to retrieve the appropriate data for the local money field, and then modifying the final update or select statement to include the USD equivalent calculation.
I recently realized that we could save a step and do our FX calculations “automatically” by just including the USD equivalent columns in our table variables as computed columns.
If you are already using temporary tables or table variables in a “Set By Agonizing Set” SBAS style (perhaps I just coined this?), using this technique may allow you to keep your code a bit cleaner.
Here is an example:
DECLARE @FXRatesToUSD TABLE (
Currency VARCHAR(3) NOT NULL PRIMARY KEY,
RateToUSD FLOAT);
--Approximate FX Rates in October 2011
INSERT INTO @FXRatesToUSD
VALUES ('USD',1.0), ('GBP',0.62), ('NOK',5.43);
DECLARE @Example TABLE (
VehicleID INT NOT NULL PRIMARY KEY,
Currency VARCHAR(3) NULL,
PriceLocal MONEY NULL,
FXRate FLOAT NULL,
--computed column in the table variable
PriceUSD AS CAST(ROUND(PriceLocal / FXRate,-3) AS MONEY)
);
--Insert sample data
INSERT INTO @Example (VehicleID, Currency, PriceLocal)
VALUES (1, 'USD', 45000),
(2, 'USD', 14000),
(3, 'USD', 31000),
(4, 'GBP', 20000),
(5, 'NOK', 190000);
--take a look at the table
SELECT 'FX Rates not yet set' AS [example], * FROM @Example;
--merge in our FX rates
UPDATE e1
SET e1.FXRate = fx.RateToUSD
FROM @Example e1
INNER JOIN @FXRatesToUSD AS fx ON fx.Currency = e1.currency;
--now that the FX rates are present, our USD prices are available
SELECT 'FX Rates are set' AS [example], * FROM @Example;
--discount one of the cars
UPDATE @Example SET PriceLocal = 18000 WHERE VehicleID = 4;
--You should see that both prices are updated now for vehicle 4.
SELECT 'Updated GBP and USD price for Vehicle 4' AS [example],
* FROM @Example;
One of the only problems with this approach is that it does not seem possible to “chain” computed columns. If I added a maximum USD discount field such as this to the table:
MaxDiscountUSD AS PriceUSD * 0.1
I would unfortunately get this error.
Msg 1759, Level 16, State 0, Line 11
Computed column 'PriceUSD' in table '@Example' is not allowed to be used in another computed-column definition.
In this case, it would seem that this technique should only be used for very simple one-pass cases or in conjunction with deterministic scalar UDFs that modularize the underlying logic.
