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