Search code examples
sql-servert-sqlevaluate

String Expression to be evaluated to number

I need to write a TSQL user defined function which will accept a string and return a number.

I will call the function like dbo.EvaluateExpression('10*4.5*0.5') should return the number 22.5

Can any one help me to write this function EvaluateExpression.

Currently I am using CLR function which I need to avoid.

Edit1

I know this can be done using stored procedure, but I want to call this function in some statements ex: select 10* dbo.EvaluateExpression('10*4.5*0.5')

Also I have around 400,000 formulas like this to be evaluated.

Edit2

I know we can do it using osql.exe inside function as explained here. But due to permission settings, I can not use this also.

Solution

  • I don't think that is possible in a user defined function.

    You could do it in a stored procedure, like:

    declare @calc varchar(max)
set @calc = '10*4.5*0.5'

declare @sql nvarchar(max)
declare @result float
set @sql = N'set @result = ' + @calc
exec sp_executesql @sql, N'@result float output', @result out
select @result

    But dynamic SQL, like exec or sp_executesql, is not allowed in user defined functions.