# Calculate from Table Column in Sql server 2008 R2

• ### Question

• User-448512826 posted

Hi,

here is my two table

 Id Name Rate 1 Basic 1000 2 HRA 200 3 PTO 100

 ID Formula Remark 1 (1+2-3)*12% 12 % of Basic+HRA-PTO 2 1*14% 14 % of Bacis 3 ((1+2)*(2+3))/100

I want a function which calculates from Formula Column of second table and value fetch from the 1st table like 1=1000,2=200 like this and return the calculated Value in SQL server 2008 R2.

all should be <g class="gr_ gr_422 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="422" data-gr-id="422">dynamically</g> i.e from Formula column it should calculate.

Regards

and

Tuesday, February 5, 2019 11:22 AM

• User-595703101 posted

I created a UDF function that splits formula and fetches parameters name then replaces parameters with their values as follows

```create or alter function replaceFormula (
@formula nvarchar(max)
) returns nvarchar(max)
begin

declare @i int
declare @rate int
declare @parameter varchar(50)
declare @newformula nvarchar(max) = ''
declare @tmp nvarchar(max) = @formula

set @i = PATINDEX('%{parameter%', @tmp)
while @i > 0
begin
set @newformula = left(@tmp, @i-1)
set @tmp = SUBSTRING(@tmp, @i, len(@tmp)-@i+1)
set @i = CharIndex('}', @tmp)
set @parameter = SUBSTRING(@tmp,1,@i)
set @parameter = SUBSTRING(@parameter, 2, len(@parameter) - 2)
select @rate = rate from aspnet.formula_parameters where id = @parameter
set @newformula = CONCAT(@newformula, @rate, SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1) )
set @tmp = @newformula
set @i = PATINDEX('%{parameter%', @tmp)
end
return @newformula
end
go```

Please note, I designed this solution for following conditions

Parameter are like 'parameter1', 'parameter2'

In formulas parameters are used with curly brackets aroung like {parameter1}*{parameter2}

Please check following sample data as well

```create table aspnet.formula_parameters (
Id varchar(30) not null primary key,
Name nvarchar(100) not null,
Rate int not null
)
create table aspnet.formulas (
Id int not null primary key,
Formula nvarchar(1000) not null,
Remark nvarchar(1000)
)

insert into aspnet.formulas
select 1,'({parameter1}+{parameter2}-{parameter3})*12%', '12 % of Basic+HRA-PTO'

insert into aspnet.formulas
select 2,'{parameter1}*14%', '14 % of Bacis'

insert into aspnet.formulas
select 3,'(({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100', null

insert into aspnet.formula_parameters select 'parameter1','Basic',1000
insert into aspnet.formula_parameters select 'parameter2','HRA',200
insert into aspnet.formula_parameters select 'parameter3','PTO',100
```

Then below SQL can be used to query formula data with figures

`select *, dbo.replaceFormula(formula) from aspnet.formulas `

I hope it helps

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, February 6, 2019 8:12 AM
• User-595703101 posted

OK :) Since we cannot use SP_EXECUTESQL etc to evaluate string expression into a numeric value within UDF functions, I prepared following SQL cursor code for you

You will see I use sp_executesql with output parameter definitions that calculates the numeric value of the formula

Please check if it helps for you

```drop table if exists  ##formula

select *, dbo.replaceFormula(formula) as f, NULL as val
into ##formula
from aspnet.formulas  where 1 = 0

DECLARE @Id Int
DECLARE @Val Int
Declare @formula nvarchar(max)
declare @sql nvarchar(max)

DECLARE formula_cursor CURSOR FAST_FORWARD FOR
select Id, dbo.replaceFormula(formula) from aspnet.formulas
OPEN formula_cursor

FETCH NEXT FROM formula_cursor INTO @Id, @formula

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'SELECT @val = ' + convert(nvarchar(max), replace(@formula,'%','*1.0/100'))
EXECUTE sp_executesql @sql, N'@val int OUTPUT', @Val = @Val OUTPUT
print @Val
insert into ##formula select *, @formula, @Val from aspnet.formulas where Id = @Id

FETCH NEXT FROM formula_cursor INTO @Id, @formula
END

CLOSE formula_cursor
DEALLOCATE formula_cursor

select * from ##formula
```

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 8, 2019 11:02 AM

### All replies

• User-2082239438 posted

is there any solution?

can someone help me.

You will need to re-structure your table. As per the current table structure, it makes difficult to define any SQL query.

As per my observation, if you will have only three payment/formula head then you will need to define the case by case logic in the SQL.

Wednesday, February 6, 2019 6:57 AM
• User-448512826 posted

Hi,

payment not fix.it is dynamic.so as per requirement there is one master table which store Rate as per PayId and there is another table which store the calculation type i.e Fixed or Calculated then if calculated then based on a formula it should calculate and return calculated value.

Regards,

Wednesday, February 6, 2019 7:39 AM
• User-595703101 posted

I created a UDF function that splits formula and fetches parameters name then replaces parameters with their values as follows

```create or alter function replaceFormula (
@formula nvarchar(max)
) returns nvarchar(max)
begin

declare @i int
declare @rate int
declare @parameter varchar(50)
declare @newformula nvarchar(max) = ''
declare @tmp nvarchar(max) = @formula

set @i = PATINDEX('%{parameter%', @tmp)
while @i > 0
begin
set @newformula = left(@tmp, @i-1)
set @tmp = SUBSTRING(@tmp, @i, len(@tmp)-@i+1)
set @i = CharIndex('}', @tmp)
set @parameter = SUBSTRING(@tmp,1,@i)
set @parameter = SUBSTRING(@parameter, 2, len(@parameter) - 2)
select @rate = rate from aspnet.formula_parameters where id = @parameter
set @newformula = CONCAT(@newformula, @rate, SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1) )
set @tmp = @newformula
set @i = PATINDEX('%{parameter%', @tmp)
end
return @newformula
end
go```

Please note, I designed this solution for following conditions

Parameter are like 'parameter1', 'parameter2'

In formulas parameters are used with curly brackets aroung like {parameter1}*{parameter2}

Please check following sample data as well

```create table aspnet.formula_parameters (
Id varchar(30) not null primary key,
Name nvarchar(100) not null,
Rate int not null
)
create table aspnet.formulas (
Id int not null primary key,
Formula nvarchar(1000) not null,
Remark nvarchar(1000)
)

insert into aspnet.formulas
select 1,'({parameter1}+{parameter2}-{parameter3})*12%', '12 % of Basic+HRA-PTO'

insert into aspnet.formulas
select 2,'{parameter1}*14%', '14 % of Bacis'

insert into aspnet.formulas
select 3,'(({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100', null

insert into aspnet.formula_parameters select 'parameter1','Basic',1000
insert into aspnet.formula_parameters select 'parameter2','HRA',200
insert into aspnet.formula_parameters select 'parameter3','PTO',100
```

Then below SQL can be used to query formula data with figures

`select *, dbo.replaceFormula(formula) from aspnet.formulas `

I hope it helps

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, February 6, 2019 8:12 AM
• User-448512826 posted

hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">eralper</g>,

it <g class="gr_ gr_93 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="93" data-gr-id="93">throw</g> me <g class="gr_ gr_92 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="92" data-gr-id="92">a error</g> when <g class="gr_ gr_94 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="94" data-gr-id="94">i</g> execute this.

error--Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the <g class="gr_ gr_103 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="103" data-gr-id="103">nvarchar</g> value '(' to data type int.

execute like select *, dbo.replaceFormula(formula) from formulas

also when <g class="gr_ gr_127 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="127" data-gr-id="127">i</g> check with my own it <g class="gr_ gr_142 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="142" data-gr-id="142">throw</g> me <g class="gr_ gr_141 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="141" data-gr-id="141">error</g>

Conversion failed when converting the <g class="gr_ gr_148 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="148" data-gr-id="148">nvarchar</g> value '*12%' to data type int.

 PAYID PAYNAME TYPE FORMULA PARAMETER VALUE 1 BASIC LUM NULL parameter1 NULL 2 PF FOR {parameter1}*12% parameter2 NULL 3 HRA LUM NULL parameter3 NULL 4 ESI MAP 1;2 parameter4 10

just test me out and yes <g class="gr_ gr_211 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="211" data-gr-id="211">i</g> changed below <g class="gr_ gr_209 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="209" data-gr-id="209">line</g> as <g class="gr_ gr_208 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="208" data-gr-id="208">concat</g> not support in <g class="gr_ gr_206 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="206" data-gr-id="206">sqlserver</g> 2008

set @newformula = @newformula+ @rate+ SUBSTRING(@tmp, @i+1, <g class="gr_ gr_207 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="207" data-gr-id="207">len</g>(@tmp)-@i+1)

+ replace with <g class="gr_ gr_1412 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="1412" data-gr-id="1412">concat</g>.

Friday, February 8, 2019 2:41 AM
• User-595703101 posted

Yes, that is because when you are using " + " to concatenate a character value with a numeric value

Please, convert numeric values before using them in a " + " concatenation operation as follows

`convert(nvarchar(max), @rate)`

like in

`set @newformula = @newformula+ convert(nvarchar(max), @rate)+ SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1)`

The SQL CONCAT() function (in SQL Server 2012 and later) takes care of data type conversions

Friday, February 8, 2019 5:50 AM
• User-448512826 posted

Hi Eralper,

Thank you so much for your support.

but <g class="gr_ gr_90 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="90" data-gr-id="90">i</g> need here the actual value <g class="gr_ gr_144 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="144" data-gr-id="144">not</g> FORMULA itself.

 Id Formula Remark (No column name) CORRECT VALUE 1 ({parameter1}+{parameter2}-{parameter3})*12% 12 % of Basic+HRA-PTO (1000+200-100)*12% 132 2 {parameter1}*14% 14 % of Bacis 1000*14% 140 3 (({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100 NULL ((1000+200)*(200+100))/100 3600

i just pass FORMULA and RATE in that function and it should return me the Result only after calculation.

Thanks,

Friday, February 8, 2019 10:18 AM
• User-595703101 posted

OK :) Since we cannot use SP_EXECUTESQL etc to evaluate string expression into a numeric value within UDF functions, I prepared following SQL cursor code for you

You will see I use sp_executesql with output parameter definitions that calculates the numeric value of the formula

Please check if it helps for you

```drop table if exists  ##formula

select *, dbo.replaceFormula(formula) as f, NULL as val
into ##formula
from aspnet.formulas  where 1 = 0

DECLARE @Id Int
DECLARE @Val Int
Declare @formula nvarchar(max)
declare @sql nvarchar(max)

DECLARE formula_cursor CURSOR FAST_FORWARD FOR
select Id, dbo.replaceFormula(formula) from aspnet.formulas
OPEN formula_cursor

FETCH NEXT FROM formula_cursor INTO @Id, @formula

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'SELECT @val = ' + convert(nvarchar(max), replace(@formula,'%','*1.0/100'))
EXECUTE sp_executesql @sql, N'@val int OUTPUT', @Val = @Val OUTPUT
print @Val
insert into ##formula select *, @formula, @Val from aspnet.formulas where Id = @Id

FETCH NEXT FROM formula_cursor INTO @Id, @formula
END

CLOSE formula_cursor
DEALLOCATE formula_cursor

select * from ##formula
```

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 8, 2019 11:02 AM
• User-448512826 posted

Hi,

just confuse below <g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="4" data-gr-id="4">line</g>

`set @sql = 'SELECT @val = ' + convert(nvarchar(max), replace(@formula,'%','*1.0/100'))it replace formula with '%','*1.0/100' but how can i do it dynamically like any operator i.e /,{,} etc.can you please clear me . or is there any simple way or is it not possible with one function?Thanks,`
Friday, February 8, 2019 11:31 AM
• User-595703101 posted

% is not used as the percentage operator in SQL Server, it is for modulo calculation (for arithmetic operators please refer to link)

So I had to replace it

Somehow we have to replace or convert human understandable formula to SQL engine language :)

Do you have other types of calculations? Maybe we can check

Friday, February 8, 2019 11:52 AM
• User-448512826 posted

ok..thanks<g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation multiReplace" id="10" data-gr-id="10">..</g>

can you please combine function and cursor in one as another function or any other else.

just pass two parameters like formula and Rate and return calculated value.  :)

<g class="gr_ gr_398 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="398" data-gr-id="398">i</g> just confuse how to combine.. :)

Thanks,

Friday, February 8, 2019 12:11 PM