XPath abs function not working while using in SQL Server-CodePudding

We have a stored procedure

CREATE PROCEDURE [dbo].[usp_s_eval_expr] 
    @formula NVARCHAR(max),
    @result SQL_VARIANT OUTPUT
AS 
BEGIN
    DECLARE @sql NVARCHAR(max) 
    DECLARE @cal_value FLOAT
    
    SET @sql = N'DECLARE @x xml=''''SELECT @cal_value=CAST(@x.query(''' @formula ''') as nvarchar(max))'
    EXEC sp_executesql @sql,N'@cal_value FLOAT OUTPUT',@cal_value OUTPUT

    SET @result = @cal_value
    SELECT @result
END
GO

It is created to evaluate mathematical expressions, I understand that there are better ways to solve the expression, it is deployed in production and I don't have the right to modify it.

It works with most of the functions but is throwing errors while using it to find the absolute value of a number.

For example,

DECLARE @formula NVARCHAR(max) = ' abs(-1.5) '
DECLARE @result SQL_VARIANT 
EXEC [usp_s_eval_expr] @formula, @result OUTPUT
SELECT @result

This throws the error following error whereas ceiling and round functions work well.

Msg 50000, Level 16, State 1, Procedure usp_s_eval_expr, Line 32 [Batch Start Line 0]
XQuery [query()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:abs()'

Is there any way to achieve the absolute value of a number order than checking less than 0, the reason why I don't want to check less than is the expression that results in -1.5 is very big, and I don't want to use it twice to achieve the abs functionality.

CodePudding user response：

According to the documentation available by download from https://learn.microsoft.com/en-us/openspecs/sql_standards/MS-SQLSTANDLP/89fb00b1-4b9e-4296-92ce-a2b3f7ca01d2 SQL Server does not support the fn:abs() function.

Also: are you sure about the namespace {http://www.w3.org/2004/07/xpath-functions}. That's from an early draft of XPath 2.0, not the final W3C spec. But I don't think SQL Server ever implemented the final spec anyway.

Another way to get the absolute value might be to convert to a string and strip the minus sign.