"Eval"-like function in SQL Server

[SuryaF]

Hi,
In one of my tables I have a string field to record a calculation formula. For example "([Cost]+[Markup])*(1+[Tax])+10".
I need to get the value for this formula.
For example, when I have Cost=10 and Markup=5 and Tax=0.1 I need it to calculate somehow to whatever it is (I think it's 26.5).
In Access I replace in the string each field with its value, I get the string "(10+5)*(1+0.1)+10" and I use Eval() to get the value of this calculation. How can I do this in SQL Server?
Thanks a lot!

 

[jrbarnett]

Just put the expression in a column, eg:

SELECT ([Cost]+[Markup])*(1+[Tax])+10 As Result
FROM YourTable

would give the column a heading of "Result"

John

 

[SuryaF]

Hi jrbarnett,
My formula is different for each row in the table. That was just an example. Any ideas?

 

[vbkris]

try this:
1. pass the data to a function (UDF)
2. in the function call the sp_executesql method (thread183-1246610)
3. return back the calculated value in the function

Known is handfull, Unknown is worldfull

 

[SuryaF]

Hi vbkris,
Thanks for your suggestion. I've created the folowing function:

CREATE FUNCTION CalcTPFormulaFN (@Formula as nvarchar(1000),@MCT as float,@SHC as float,@PR as float,@DF as float,@EMNC as float)  
RETURNS float AS  
BEGIN 
DECLARE @strSQL as nvarchar(1000)
DECLARE @res as float,@par as nvarchar(1000)
SET @par='@res FLOAT OUTPUT'
SET @strSQL ='SELECT @res=' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@formula,'[MCT]',ISNULL(@MCT,0)),'[SHC]',ISNULL(@SHC,0)),'[PR]',ISNULL(@PR,0)),'[DF]',ISNULL(@DF,0)),'[EMNC]',ISNULL(@EMNC,0))
EXECUTE sp_executesql @strSQL,@par,@res OUTPUT
RETURN @res
END

I get the error:
Only functions and extended stored procedures can be executed from within a function.
Any ideas?

 

[vbkris]

hi,

sorry for misleading you, i thought sp_executesql was an extended SP (its listed in the extended SP section!). seems like it isnt. i will do some research on the same and get back to you...

Known is handfull, Unknown is worldfull