1

I need to calculate formulas that are stored in a table, using the function. I found an option to calculate using XML.VALUE, but I can't figure out how to do it using a variable.

I tried:

select cast('' as xml).value('(86.4 div 10000)*(7545 div 14968)*(1)', 'float')

It works, the result is 0.004355

But when I try to use a variable:

declare @formula varchar(max) = '(86.4/10000)*(7545/14968)*(1)'
set @formula = replace(@formula, '/',' div ')
select cast('' as xml).value(@formula, 'float')

I get an error:

SQL Error [8172] [S0001]: The argument 1 of the XML data type method "value" must be a string literal.

declare @formula varchar(max) = '86.4*1'
declare @xml xml = '<Formula>' + @formula + '</Formula>' 
select @xml.value('(//Formula)[1]', 'float')

SQL Error [8114] [S0005]: Error converting data type nvarchar to float.

Using dynamic SQL, I can't do it because I can't use "exec" in the function.

So if that doesn't work, the only option I have, I think, is to calculate using dynamic SQL and then join the results with the existing table, but I'd rather use the function if I can

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
urys
  • 43
  • 6
  • 1
    This [answer](https://stackoverflow.com/a/43102781/724039) seems to work. It uses: `declare @formula varchar(max) = '(86.4 / 10000) * (7545.0 / 14968) *(1)'; exec('select ' + @formula);`, in fact this question might even be a duplicate of this one... ? – Luuk Oct 30 '22 at 10:33
  • I *think* the issue here is that the intention is to do this in a scalar function - although it's far from being clear. – Stu Oct 30 '22 at 11:12
  • Yes, I would prefer a scalar function. I've read about CLR, but I must have missed the post @Luuk refers to. I'll try to look into it, thanks. – urys Oct 30 '22 at 12:06
  • Is there anything well-defined about the formulas? Are they always `(a / b) * (c / d)`? Is there some other way you can define how they look? – Charlieface Oct 30 '22 at 12:33
  • you can't use this method to do a dynamic eval as you were probably hoping to do. If you need to evaluate formulas held in strings dynamically without using dynamic SQL then the XML method doesn't give you anything – Martin Smith Oct 30 '22 at 16:17

1 Answers1

2

It's not possible, sorry. I know it feels like you should be able to do this with XML, because if it were a normal T-SQL function you could, but it's a special XML beast that won't behave dynamically nomatter what you do. Here's a response to an analogous question: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b72d0430-44ff-4ded-a3f2-04591ff32bcc/xquery-syntax-to-evaluate-math-formula?forum=transactsql

In short, the only means by which you can dynamically evaluate a formula stored in a column is with eval(), CLR (i.e. a .net module), Python/R (via machine learning services), or writing your own parser in T-SQL (don't do this, please!).

The more detailed answer is that the string you are parsing in to the .value() method (the same is true if you used the .query() method) is being interpreted as XQuery code. The error you are seeing is because these methods can only accept plain, primative, literal strings. For instance:

Select convert(xml, '').query('1 + 2') --returns 3, works fine
Select convert(xml, '').query('1 + 2' + '') -- error, must be string literal, no calculations allowed
Declare @formula varchar(10) = '1 + 2')
Select convert(xml, '').query(@formula) -- also error, must be string literal

This is deliberate, and by design to prevent what you are trying to do, because the XQuery also needs to be compiled by the engine before the query begins to execute. It can have dynamic parameters from the SQL like this:

Select convert(xml, '').query('sql:column("num") +  2')
from (
    select num = 1
) p

But the compilation of the XQuery (the step that was performing your calculation) still only happens once. If you try and push the evaluating down to the XQuery layer you will hit the same roadblocks, just by different names. e.g.

Select convert(xml, '').query('sql:column("formula") * 1')
from (
   select formula = '1 + 2'
) p

Returns error:

argument of '*' must be of a single numeric primitive type

and similarly:

Select convert(xml, '').query('sql:column("formula") cast as xs:float ?')
from (
   select formula = '1 + 2'
) p

Returns an empty XML string.

There are also more sophistocated XQuery functions that were introduced in later versions such as fn:function-lookup() and fn:apply() which could potentially be used for dynamic binding to do some sort of formula evaluation, but these are not available in XQuery 1.0 on which the SQL implementation is based. Further reading: https://learn.microsoft.com/en-us/sql/xquery/xquery-language-reference-sql-server?source=recommendations&view=sql-server-ver16

James Mc
  • 549
  • 6
  • 10