Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
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
add comment

2 Answers

up vote 4 down vote accepted

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 |
share|improve this answer
1  
+1 for creative use of a trigger. –  Max Vernon Nov 8 '13 at 18:50
    
This is the best answer i can get .. thx for giving such gr88 idea –  Dhaval Nov 11 '13 at 12:16
add comment

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 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.