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
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;'


Results:

|                                                                                                                                                                                                                                 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


Results:

| 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 |