Skip to content
Advertisement

Function to convert units based on conversion table

There is a very simple table in the database with metric unit conversions, that looks like:

| UnitFrom | UnitTo | Scale |
|    m     |   mm   |  1000 |
|    m     |   cm   |   100 |
    etc.

I was thinking about creating a function to convert units that calculates the conversion from the table based on inputs (Unit, FromUnit, ToUnit).
For example Conv_Units(0.1, 'm', 'cm') returns 100.

Is this even possible? What I came up with is following, but it does not work:

CREATE FUNCTION dbo.Conv_Units
(
    [@Unit] float
    , [@FromUnit] nvarchar(20)
    , [@ToUnit] nvarchar(20)
);
RETURNS float
AS
BEGIN

    DECLARE @ConvUnit float;
    DECLARE @Unit float;
    DECLARE @FromUnit nvarchar(20);
    DECLARE @ToUnit nvarchar(20);
    DECLARE @Scale float = (select Scale from dbo.UnitsConversion where FromUnit = @FromUnit and ToUnit = @ToUnit);

    SELECT @ConvUnit = @Unit * @Scale

    RETURN @ConvUnit

END
GO

Advertisement

Answer

Thanks @GSerg for the solution.

That was it:

CREATE FUNCTION dbo.Conv_Units(@Unit float, @FromUnit nvarchar(20), @ToUnit nvarchar(20))
RETURNS float
AS
BEGIN
    DECLARE @ConvUnit float;
    DECLARE @Scale float = (select Scale from dbo.UnitsConversion where FromUnit = @FromUnit and ToUnit = @ToUnit)

    SELECT @ConvUnit = @Unit * @Scale

    RETURN @ConvUnit

END
GO
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement