Evaluate a formula and calculate a variable value

Bill YU

Hi,

Is it possible to evaluate a varchar formula and calculate variable value i.e.

I have a formula saved in a varchar variable: (202/7.3 + 67) * 2 - 12 + 6+@param = 210.67

How can I get @param result

Thanks

Manivannan.D.Sekaran

Use the following query..

Code Snippet

Declare @result as float;

Declare @param as float;

Set @param = 100

Exec sp_executesql

N'Set @ReturnResult = (202/7.3 + 67) * 2 - 12 + 6 + @Inputparam'

,N'@Inputparam as float, @ReturnResult as float output'

,@param

,@result OUTPUT

Select @result

clintz

hi,
try this query..

declare @param float, @total float
select @total = 210.67,@param = @total - ((202/7.3 + 67) * 2 - 12 + 6)
select @param

-clintz

Manivannan.D.Sekaran

ClintZ,

Yes its looks good.

I read these lines...Do you noticed

Bill YU wrote:

I have a formula saved in a varchar variable: (202/7.3 + 67) * 2 - 12 + 6+@param = 210.67

Arnie Rowland

Mani,

The result of your formula is 283.34, and the result of clintz formula is 27.33.

I think that there is no question that clintz's formula more closely parallels the OP needs -and that yours misses the mark on this one...

In order to solve the equation, the OP needs to revise the 'formula' so that it can be solved.

clintz

ooops sorry,
anyway, try this..

declare @formula varchar(50)
declare @formula2 varchar(50)
set @formula = '(202/7.3 + 67) * 2 - 12 + 6+@param = 210.67'
declare @total varchar(50)
set @total = ltrim(substring(@formula, charindex('=', @formula) + 1, len(@formula)))
select @formula2 = '(' + substring(replace(@formula, '@param', '(-' + @total + ')'), 0, charindex('=', @formula) + 2) + ') * -1'
exec('select ' + @formula2)

-clintz

clintz

hi,
this one's better and a bit shorter..

declare @formula varchar(50)
declare @formula2 varchar(50)
--set @formula = '(202/7.3 + 67) * 2 - 12 + 6+@param = 210.67'
--set @formula = '@param + 1=50'
set @formula = '(100*2) + 50 - @param=50'
declare @total varchar(50)
set @total = right(@formula, len(@formula) - patindex('%=%',@formula) )
set @formula2 = '(' + replace(left(@formula, len(@formula) - (len(@total) + 1)), '@param', '(-' + @total + ')') + ') *-1'
select @formula2
exec('select ' + @formula2)

-clintz

hunchback

Try:

Code Snippet

declare @sql nvarchar(4000)

declare @param numeric(10, 4)

set @sql = N'set @param = -1 * ((202/7.3 + 67) * 2 - 12 + 6 - 210.67)'

exec sp_executesql @sql, N'@param numeric(10, 4) output', @param output

select @param

AMB

Bill YU

Thanks Manivannan,

But what I need is reversed calculation, which mens that I have the @result, need @param to be output.

Bill

hunchback

Hi Bill,

You can rearrange the formula in order to get @param. See my post.

AMB

Bill YU

Thanks everyone,

In my case, the formulas are user defined, they are totally dynamic and may be more complex, for example:

declare @formula varchar(MAX)

set @formula = ' (202/7.3 + 67) * 2 - (12 - (6 + @param*2))/7 = 210.67'

Bill

hunchback

Hi Bill,

As I told you, you can rearrange the formula in order to get @param, like a math problem, right.

AMB

Bill YU

You are correct, but how to evaluate it

Louis Davidson

SQL Server isn't really a mathmatics library, at all. So there isn't a good way to evaluate:

(202/7.3 + 67) * 2 - 12 + 6+@param = 210.67

Using T-SQL. As everyone is trying to say, you can easily rewrite this as:

select -1 *( (202/7.3 + 67) * 2 - 12 + 6 - 210.67) as [@param]

To get the answer. So however the formula is created, you could simply rearrange the query to move the variable out of the formula (as we all learned in Algebra, all those years ago.)

If your question is how can you do this with T-SQL with no work on your part, then you "could" do it with guessing.

declare @param decimal(10,2)

declare @result decimal(10,2)

set @param = 1

select @result = (202/7.3 + 67) * 2 - (12 - (6 + @param*2))/7

select @result

if @result < 210.67

set @param = @param - 2

else

set @param = @param + 2

select @result = (202/7.3 + 67) * 2 - (12 - (6 + @param*2))/7

This would be a rediculous way of solving a problem, but you could do it

If you are stuck with this formula, you might look at .NET and see if there are libraries to do this sort of thing. It would be child's play to implement this using a CLR function if one existed.

clintz

Bill YU wrote:

You are correct, but how to evaluate it

hi, have you tried my post..

- clintz