execute formula stored as string

[japss]

I have got a column in my table which stores mathematical formulas as strings. The base question is how do I evaluate the "string formula" to get result?

For example, I got a value like '2*5+2*5*4'.
I need a way to evaluate this string within an SQL Server stored procedure to get the result as 50

Declare @weight as string
Declare @output as numeric(18,2)

SET @weight = '2*5+2*5*4'.

Set @output = somefunction(@weight)

OutPut should be 50.

Thanks in advance for your help

[ck9663]

Two ways:

1. Use sp_executesql and use it's ability to return a parameter.


2. Take the long way:

declare @strFormula varchar(150), @intLength int, @inWidth int, @intHeight int, @intResult int
 
set @strFormula = 
    '
    declare @FormulaResult int
    set @FormulaResult = [Length] * [Width] * [Height]
    select @FormulaResult as FormulaResult
    '
 
select  @intLength = 2, @inWidth = 5, @intHeight = 3
 
set @strFormula = replace(replace(REPLACE(@strFormula,'[Length]',cast(@intLength as varchar(3))),'[Width]', cast(@inWidth as varchar(3))),'[Height]',CAST(@intHeight as varchar(3)))
 
select @strFormula as strFormula
 
exec  (@strFormula)
 
 

Good luck!

-- CK