# Metric to standard unit conversion in SQL Server

I am giving simplified version of my two table which are as follow.

tblRecords

``````ID  unit  val
--- ---   ---
1   KV    3
1   BAR   4
2   KV    7
2   KG    5
3   C     37
``````

tblUnit

``````unit    convUnit    formula
---     ---         ---
KV      CV          @val * .865
KG      lbs         @val / 2.205
BAR     PSI         @val / 14.504
C       F           @val * 9/5 + 32
``````

I will not be able to change table structure.

I know which unit will be converted in which unit (i.e. I know KV will be converted to CV, KG to lbs etc..).

Please note that formula is a `varchar` column and I have reference from table `tblRecords.unit` to `tblUnit.unit`

But my question is how to calculate the formulas on the fly in single query. i.e. I want following output.

``````ID  unit    val     convVal(column will be counted on the fly using formula stored in tblUnit.formula field)
--- ---     ---     ---
1   KV      3       2.595
1   BAR     4       0.27578599
2   KV      7       6.055
2   KG      5       2.267573696
3   C       37      98.6
``````
-
I always thought that metric was the international standard (and the US and British (Imperial) units are the oddballs....) – marc_s Nov 8 '13 at 18:28

There is no direct way to implement this in SQL Server, as dynamic SQL in functions is not permitted. However, as your tblUnit will most likely rarely change you can go an indirect way:

What we need is a function that can handle the conversion based on the formulas in the table. If we just could hard-code those formulas in the function and somehow update the function every time the table changes, the problem would be solved. So, can we?

Yes, we can:SQL Fiddle

MS SQL Server 2008 Schema Setup:

``````CREATE TABLE dbo.tblRecords
([ID] int, [unit] varchar(3), [val] DECIMAL(10,4))
;

INSERT INTO dbo.tblRecords
([ID], [unit], [val])
VALUES
(1, 'KV', 3),
(1, 'BAR', 4),
(2, 'KV', 7),
(2, 'KG', 5),
(3, 'C', 37)
;

CREATE TABLE dbo.tblUnit
([unit] varchar(3), [convUnit] varchar(3), [formula] varchar(15))
;
GO
CREATE FUNCTION dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))
RETURNS TABLE
AS
RETURN
SELECT @val convVal;
GO
CREATE TRIGGER updateFnConvert ON dbo.tblUnit AFTER INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
' RETURNS TABLE AS'+
' RETURN SELECT CASE @unit'+
(SELECT ' WHEN '''+unit+''' THEN '+formula
FROM dbo.tblUnit
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
' END convVal;'
EXEC(@cmd);
END

GO
``````

This creates the two tables, a fnConvert function stub and a trigger on the tblUnit table.

When we now alter the contents of the table with INSERT, UPDATE or DELETE like this:

Query 1:

``````INSERT INTO dbo.tblUnit
([unit], [convUnit], [formula])
VALUES
('KV', 'CV', '@val * .865'),
('KG', 'lbs', '@val / 2.205'),
('BAR', 'PSI', '@val / 14.504'),
('C', 'F', '@val * 9/5 + 32')
;
``````

the trigger fires and generates a dynamic sql statement like this:

Query 2:

``````SELECT 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
' RETURNS TABLE AS'+
' RETURN SELECT CASE @unit'+
(SELECT ' WHEN '''+unit+''' THEN '+formula
FROM dbo.tblUnit
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
' END val;'
``````
``````|                                                                                                                                                                                                                                 COLUMN_0 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4)) RETURNS TABLE AS RETURN SELECT CASE @unit WHEN 'KV' THEN @val * .865 WHEN 'KG' THEN @val / 2.205 WHEN 'BAR' THEN @val / 14.504 WHEN 'C' THEN @val * 9/5 + 32 END val; |
``````

and then executes that statement. That statement replaces the function with a version containing all the current formulas.

Now we can just use the function in any query like this:

Query 3:

``````SELECT *
FROM dbo.tblRecords AS TR
JOIN dbo.tblUnit AS TU
ON TR.unit = TU.unit
CROSS APPLY dbo.fnConvert(TU.unit,TR.val) AS FC
``````
``````| ID | UNIT | VAL | CONVUNIT |         FORMULA |     CONVVAL |
|----|------|-----|----------|-----------------|-------------|
|  1 |   KV |   3 |       CV |     @val * .865 |       2.595 |
|  1 |  BAR |   4 |      PSI |   @val / 14.504 |  0.27578599 |
|  2 |   KV |   7 |       CV |     @val * .865 |       6.055 |
|  2 |   KG |   5 |      lbs |    @val / 2.205 | 2.267573696 |
|  3 |    C |  37 |        F | @val * 9/5 + 32 |        98.6 |
``````
-

Looking up a formula for a conversion like this seems really bad. I'd use a function or stored procedure to do this and put the logic there. A simple `SELECT` could be written like so, but this does depend on how many cases you have to convert.

``````SELECT id, unit, val,
case when unit = 'KV'  then val * 0.865
when unit = 'BAR' then val / 14.504
when unit = 'KG'  then val / 2.205
when unit = 'C'   then val * 9/5 + 32
end as conval
FROM tblRecords
``````
-