SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Save a step: Using Computed Columns in a Table Variable (or Temp Table)

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:



      RateToUSD FLOAT);


--Approximate FX Rates in October 2011


      VALUES ('USD',1.0), ('GBP',0.62), ('NOK',5.43);




      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


      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.

New York City .NET

The author of the NYCdotNet blog is a VB.NET and SQL server developer living and working in New York City. The author seeks to promote clean, readable, efficient code in both VB and SQL from design to development, deployment, and application maintenance.


Posted by Anonymous on 31 October 2011

Pingback from  Dew Drop – October 31, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.