如何在TSQL函数中用变量结合XML.value计算存储的公式？

问题核心 #

• XML.value方法的第一个参数（XPath表达式）必须是字符串字面量，无法通过变量传递动态计算式，这是SQL Server的设计限制
• 将公式存入XML节点后，value方法仅返回字符串内容，无法直接解析计算表达式

可行方案 #

方案1：CLR用户定义函数（推荐） #

CLR函数可以通过.NET代码执行动态表达式计算，且能在SQL自定义函数内调用，完美适配需求。

步骤1：编写CLR代码（C#）

创建类库项目，实现表达式计算方法：

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;

public class FormulaEvaluator
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlDouble EvaluateFormula(SqlString formula)
    {
        if (formula.IsNull)
            return SqlDouble.Null;
        
        try
        {
            // 使用DataTable.Compute计算表达式，支持基本四则运算
            DataTable dt = new DataTable();
            var result = dt.Compute(formula.Value, string.Empty);
            return new SqlDouble(Convert.ToDouble(result));
        }
        catch
        {
            // 公式非法时返回NULL
            return SqlDouble.Null;
        }
    }
}

步骤2：部署CLR到SQL Server

-- 启用CLR支持
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- 注册程序集（替换为你的DLL路径）
CREATE ASSEMBLY FormulaEvaluator
FROM 'C:\Your\DLL\Path\FormulaEvaluator.dll'
WITH PERMISSION_SET = SAFE;

-- 创建SQL用户定义函数
CREATE FUNCTION dbo.EvaluateFormula(@formula NVARCHAR(MAX))
RETURNS FLOAT
AS EXTERNAL NAME FormulaEvaluator.FormulaEvaluator.EvaluateFormula;

步骤3：在自定义函数中调用

CREATE FUNCTION dbo.CalculateStoredFormula(@formula NVARCHAR(MAX))
RETURNS FLOAT
AS
BEGIN
    RETURN dbo.EvaluateFormula(@formula);
END

方案2：递归CTE解析（无CLR场景） #

若无法启用CLR，可通过递归CTE手动拆分公式字符串，仅适用于简单四则运算（复杂公式需大量扩展逻辑）：

CREATE FUNCTION dbo.SimpleFormulaCalculate(@formula NVARCHAR(MAX))
RETURNS FLOAT
AS
BEGIN
    DECLARE @result FLOAT;
    -- 格式化公式，为运算符添加空格
    SET @formula = REPLACE(REPLACE(@formula, '/', ' / '), '*', ' * ');
    
    ;WITH FormulaCTE AS (
        SELECT 
            CAST(NULL AS NVARCHAR(MAX)) AS Op,
            CAST(LEFT(@formula, CHARINDEX(' ', @formula + ' ')-1) AS FLOAT) AS Val,
            LTRIM(SUBSTRING(@formula, CHARINDEX(' ', @formula + ' ')+1, LEN(@formula))) AS Remaining
        UNION ALL
        SELECT 
            LEFT(Remaining, CHARINDEX(' ', Remaining)-1),
            CASE 
                WHEN LEFT(Remaining, CHARINDEX(' ', Remaining)-1) = '*' THEN Val * CAST(SUBSTRING(Remaining, CHARINDEX(' ', Remaining)+1, CHARINDEX(' ', Remaining + ' ', CHARINDEX(' ', Remaining)+1)-CHARINDEX(' ', Remaining)-1) AS FLOAT)
                WHEN LEFT(Remaining, CHARINDEX(' ', Remaining)-1) = '/' THEN Val / CAST(SUBSTRING(Remaining, CHARINDEX(' ', Remaining)+1, CHARINDEX(' ', Remaining + ' ', CHARINDEX(' ', Remaining)+1)-CHARINDEX(' ', Remaining)-1) AS FLOAT)
                ELSE Val
            END,
            LTRIM(SUBSTRING(Remaining, CHARINDEX(' ', Remaining + ' ', CHARINDEX(' ', Remaining)+1), LEN(Remaining)))
        FROM FormulaCTE
        WHERE Remaining <> ''
    )
    SELECT @result = Val FROM FormulaCTE WHERE Remaining = '';
    
    RETURN @result;
END

注意：此示例仅处理连乘连除，如需支持加减、括号、运算符优先级，需大幅扩展递归逻辑，复杂度极高。

内容的提问来源于stack exchange，提问作者urys