Clever Geek Handbook

Calculate by formula in column

My table contains data

pk |  formula    |    OP_NME
---+-------------+---------
 1 |   x*10      |  A
 2 |   (X+Y)*10  |  B

based on the formula and the input value for x and y, I need to give the result.

For example: I am getting x = 10, Y = 20 as input, I need to give output as A = 100 and B = 300.

+3


source to share


1 answer


This may not be a complete answer, but may help you well

Declare @tab table (pk int,formula varchar(100),OP_NME varchar(10),X int)
Insert into @tab values(1,'x*10','A',Null),(2,'(X+Y)*10','B',Null)
Declare @result table (id int identity(1,1),[values] int,pk int)

Declare @s int = 1,
        @e int = (Select max(pk) From @tab),
        @sql varchar(max) = 'Declare @x int = 10,@y int = 20  ',
        @sql2 varchar(max)

While @s <= @e
Begin
    Select @sql2 = @sql + 'Select(' + Replace(Replace(formula,'x','@x'),'y','@y') + ')' From @tab Where pk = @s
    Insert into @result ([values])
    Exec(@sql2)

    Update @result
    Set pk = @s
    Where id = @@identity

Set @s = @s + 1
End

Select T.pk,T.formula,R.[values]
from @result R
join @tab T On R.pk = T.pk


Run SQL in management studio and see the result:

enter image description here

+4


source






More articles:


All Articles