SQL Server - How to perform a calculation based on a dynamic formula -

i have case need perform dynamic calculation based on particular entry in column.

the table looks like:

declare temp table (         id int,          name nvarchar(255),         const1 decimal(18,10),         const2 decimal(18,10),         const3 decimal(18,10),         const4 decimal(18,10)     ); 

i want add in field called "calculation". user has specify in field how constants applied (i.e. "const1 * const2 + (const3 - const4)").

i've got function has formula hard coded want able dynamically map table columns "calculation" field. possible? if i'm getting table entry like:

id| name         | const1 | const2 | const3 | const4 | calculation 1 | calculation1 |      5 |      3 |      2 |      9 | const1 * const2 + (const3 - const4) 

then in function, can dynamically make calculation , return output? approaching problem correct way?

thanks in advance!

you'll need use dynamic sql sp_executesql or exec. don't recall if can used in udf or not , there issues aware of, such sql injection , possible performance issues. don't have time test whether or not works in udt, want like:

declare @sql varchar(max)  select @sql = 'select ' + replace(replace(calculation, 'const1', const1), 'const2', const2)... + ' result' my_table id = 1  exec(@sql)