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